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