Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Location
    Philly
    Posts
    51

    Unanswered: 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

    thanks

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by xarfox
    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

    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
    Have you hugged your backup today?

  3. #3
    Join Date
    Jun 2004
    Location
    Philly
    Posts
    51
    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

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Search appears to be FUBAR. I found it by scrolling through. Here it is...

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


    Regards,

    hmscott
    Have you hugged your backup today?

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    nevermind......

  6. #6
    Join Date
    Jun 2004
    Location
    Philly
    Posts
    51
    didn't work

    i made the update to this thread over here:

    http://www.dbforums.com/showthread.p...26#post6233426

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I can not find my script but I swear I have done this by just moving records between msdb.dbo.sysdtspackages tables
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by xarfox
    didn't work

    i made the update to this thread over here:

    http://www.dbforums.com/showthread.p...26#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
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •