Wednesday, March 28, 2012

Migrating DTS packages

Is there anyway to copy all the DTS packages and jobs in one step from one server to another. I usvally copy one DTS package at a time. As our packages count grown bigger, it became a painful process.
If anyone knows a way to copy these packages and jobs in one then it would be very helpful.

ThanksI don't think you can for DTS: Steps I have used in the past:

Save DTS package to hard drive:
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. Dont forget to save in SQL Server.

Or...

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.

You may be able to add multi packageName, I have never play around with the code. (Maybe someone else will have an idea)

And...

As for jobs you can create a DTS package and under Task select 15 - Transfer job task and follow the steps. You can do all of your jobs at once.

Hope it works out for you.

Thanks

Lystra|||As for the DTS packages go to http://www.sqldts.com/default.aspx?242 This is a free tool to migrate the DTS packages in one step.

No comments:

Post a Comment