Friday, December 25, 2009

Move all DTS Packages between servers.

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

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*/

No comments: