Move all DTS Packages between servers.

We can move all DTS packages from one server to another server through given two methods:

If you save your dts packages under local packages, they are stored in the msdb.dbo.sysdtspackages table.
1) create a new dts package with connections to the two servers
2) create a datapump between the connection
3) because you cannot select the sysdtspackages table from the pull-down list, you need to use disconnected edit to manually modify the datapump to set the SourceTable and the DestinationTable to [msdb].[dbo].[sysdtspackages]
4) now close the disconnected edit
5) choose properties of the datapump and let dts perform the auto-mapping in the transfermations tab.
6) run it!
This will transform the rows in sysdtspackages from one server to the other, and PRESTO! All the packages have been moved, including all the version history!You could also set up a source query to select specific packages based on the name, or only the current version based on the create date.

Method 2:
Quite easy step!!!!!!1
–restore msdb db with msdbnew name from from source server to the target server then run below script:
insert into sysdtspackages ( name, id, versionid, description, categoryid, createdate, owner, packagedata, owner_sid, packagetype)
select name, id, versionid, description, categoryid, createdate, owner, packagedata, owner_sid, packagetype from msdbnew..sysdtspackages /* this is msdb from old server*/

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *