SQL Server Performance

Moving DTS packages

Discussion in 'SQL Server DTS-Related Questions' started by eklpublic, May 12, 2004.

  1. eklpublic Member

    I've moved some of our databases to a different server. There are about 30 or more DTS packages for one of the databases. How can I move them?

    Thanks
    Eliz
  2. CanadaDBA New Member

    You can SAVE AS each DTS into your new server. Open each DTS package and then from the Package menu choose Save as. But remember you need to modify connections for each DTS after you saved on new server.

    Farhad R
    CanadaDBA@Yahoo.ca
  3. Raulie New Member

    Yo check this out. When you perform a move such as the one you did if you wanted to copy all other objects as well you need to do more than just copy the production databases follow tips from this guy which will guide you through the process of moving a whole server.http://vyaskn.tripod.com/moving_sql_server.htm I personally followed some of his tips and they helped me out. But if you just want to copy DTS packages they are stored in msdb you should back up system databases if you want to tranfer objects as well.
  4. satya Moderator

    DTS Packages are stored in the msdb database (msdb.dbo.sysdtspackages), which is a system database. System databases should be backed up regularly.

    Have a look at this to save all DTS packages to files easily:
    http://www.nigelrivett.net/SaveAllDTSPackagesToFiles.html


    quote:[MSSQL City link]
    There are two ways to move DTS package:

    1. Save DTS package as file on the disk, then copy this file on the target SQL Server and load this DTS package on the target machine.

    2. Run DTS Export Wizard, and transfer this select statement:

    SELECT * FROM sysdtspackages WHERE name = 'DTSPackageName'

    from the msdb database on the source server to the msdb database on the destination server to transfer the DTSPackageName package.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. Argyle New Member

  6. satya Moderator

    Test before using this DTS backup 2000 tool, we have several issues and errors while using this tool on a test environment. And lately found few bugs have been fixed by Darren Green.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. CanadaDBA New Member

    Thank you guys! I learnt more about the article.

    Farhad R
    CanadaDBA@Yahoo.ca

Share This Page