Is there a simple way to Copy SQL Server 2000 DTS package and deploy it in another server?

Question

I am trying to copy a database from one server to another in id different location, including all the schema, data, DTS Package and Scheduled Jobs. I used Redgates SQL Packager to pack the schema and data.But I am having trouble in packing the DTS Package (which is a very complex one) in simpler way.Please advice!

Answer 1

I don't have an SQL2000 at hand right now, but I remember that you can simply open the DTS, click File \ Save As... and then save as an DTS file and that would be able to upload the DTS on another server.

Answer 2

To my knowlege there is not an easy way. There are numerous roundabout ways of doing it. I personally settled on creating a DTS package to copy my DTS packages because I had to move them on a regular basis. The file save and load method works well for one-off situations.

This link talks about moving a 2000 DTS package to a newer server but all of the methods apply for copy a DTS package to another 2000 server.

Answer 3

Whilst it's a bit of a hack, you can script DTS packages straight in and out of msdb.dbo.sysdtspackages using BCP. I don't have a system to test on right now, but the out command is something like:

bcp "select top 1 from msdb.dbo.sysdtspackages where name = '~your_package_name~' order by createdate desc" queryout c:\temp\dts.bak -n -S ~source_server_name~ -E

This command gets the latest version of the DTS definition and scripts it to a native format BCP file (you need to use -P ~password~ -U ~username~ instead of -E if you use SQL authentication).

And then to import on a second server:

bcp msdb.dbo.sysdtspackages in c:\temp\dts.bak -S ~target_server~ -E

This can cause problems if you edit the package on both servers, since the unique identifiers don't necessarily match up, and you end up with two packages with the same name. But if you only edit on one server, this works fine.

Answer 4

SQLDTS has a link on the "Administration & Management" page to transfering packages that are stored in msdb.

It also has links to a tools DTSBackup 2000 and other DTS packages to transfer DTS packages

Basically, if it isn't on this site then it is not important for anything to do with DTS...