Wednesday, March 28, 2012

migrating DTS packages

I need to migrate dts packages on one server to structured storage files and then restore them all to another server in a different network

i got the restore method down fine, but all of the backup methods I used yield an incorrect DTS package. the only way i can get the package to look right is if I manually open and save it

i would like an automated way if possible

i tried tools from sqldts.com, wrote my own, etc... none of them work right

it is sql server 2000

thanksI need to migrate dts packages on one server to structured storage files and then restore them all to another server in a different network

i got the restore method down fine, but all of the backup methods I used yield an incorrect DTS package. the only way i can get the package to look right is if I manually open and save it

i would like an automated way if possible

i tried tools from sqldts.com, wrote my own, etc... none of them work right

it is sql server 2000

thanks

What's the code you are using for your backup method? If you do a quick search on here, you should find a post that I recently wrote on a script that backsup all DTS packages for a selected instance. I have restored from one of these with no problem (other than perhaps needing to reset a few connection properties).

Regards,

hmscott|||I cannot find your recent post regarding backing up DTS packages, could you provide me with the title of the post, or re-post the code

Here is one of the methods I used:

DECLARE @.COMMAND varchar(1000)
DECLARE @.TARGETDIR varchar(500)
SET @.TARGETDIR = 'D:\DTS\'

DECLARE c1 CURSOR FOR

SELECT distinct
'DTSRUN.EXE /S '
+ CONVERT(varchar(200), SERVERPROPERTY('servername'))
+ ' /E '
+ ' /N '
+ '"' + name + '"'
+ ' /F '
+ '"' + @.TARGETDIR + replace(name,' ','_') + '.dts"'
+ ' /!X'
FROM msdb.dbo.sysdtspackages P

OPEN c1

FETCH NEXT FROM c1
INTO @.COMMAND

WHILE @.@.FETCH_STATUS = 0
BEGIN

exec xp_cmdshell @.COMMAND, no_output

FETCH NEXT FROM c1
INTO @.COMMAND
END

CLOSE c1
DEALLOCATE c1|||Search appears to be FUBAR. I found it by scrolling through. Here it is...

http://www.dbforums.com/showthread.php?t=1607904

Regards,

hmscott|||nevermind.....|||didn't work

i made the update to this thread over here:

http://www.dbforums.com/showthread.php?p=6233426#post6233426|||I can not find my script but I swear I have done this by just moving records between msdb.dbo.sysdtspackages tables|||didn't work

i made the update to this thread over here:

http://www.dbforums.com/showthread.php?p=6233426#post6233426

It's bad form, I realize, but I posted an update to your update on the other thread. Let's agree to move the discussion there for now. Perhaps we can convince a mod to merge the threads?

Regards,

hmscott

No comments:

Post a Comment