Transfer DTS package from one server (off network) to another (on network)

Question

Post reply

Transfer DTS package from one server (off network) to another (on network)

cwitucki

Old Hand

Points: 307
More actions
February 25, 2004 at 6:19 am

#161635

I have an NT4.0 server that has been taken offline and has been replaced with a 2000 server. The SQL7.0 databases were all transferred over and the 2000 server was brought on-line (same name and IP address as its predecessor). Unfortunately the DTS packages were missed. So, I need to copy them to the new server. I can't open each DTS package on the old server and save to the new server because these servers have the same name and IP and would conflict if brought on-line at the same time. I think the information for the DTS packages is stored in the msdb database but I don't know where. If I did know, I still would not know what would be the best way to get that information from the old servers msdb database to the new msdb database without hosing something. Can anyone recommend a way to copy these DTS packages without having to recreate each one from scratch?
Thanks!
Chris

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

cwitucki Old Hand Points: 307 More actions · Answer 1

Found a good article to do this:

http://www.sqldts.com/default.aspx?204

Mark Shepherd SSC Enthusiast Points: 172 More actions · Answer 2

Unplug the server from the network, start server and save DTS packages to files. Copy onto floppy and copy off floppy onto new server.

cwitucki Old Hand Points: 307 More actions · Answer 3

Thanks for the reply.

I had tried saving to floppy originally but was unclear of how to load from floppy at the new server. The article I reference above gives good details.

Thanks again.

scotttr SSC Eights! Points: 965 More actions · Answer 4

Save the DTS pkgs as a structured file and it will be saved as a file with a .DTS extension. To migrate the pkg go into the DTS designer and navigate to the directory where the files are stored and open them like any standard SQL Server saved package. To save the pkg to the database, just change the storage loaction from structured file to SQL Server. Hope this helps.

cwitucki Old Hand Points: 307 More actions · Answer 5

Thanks scotttr.

The step that I was missing with the .DTS files was right clicking on the on the "Data Transformation Services" to open the saved .DTS file. I tried opening a new DTS but could not find a way to open a saved .DTS file from that point. I right clicked on "Local Packages" but still did not see a way to open a saved .DTS file. Finally, I did as I mentioned previously and right clicked on "Data Transformation Services" to open the .DTS file. But, this was time consuming both transfering the files via floppy and opening and saving the DTS files.

Anyway the procedure I went with, which worked best and quickest for me, was to grab all the packages from the msdb database. The packages are all in the sysdtspackages table. When I found this from the above referenced article, it went without a hitch, was very quick and easy, and only took one trip with a floppy.

Thanks again.

Ivan-210990 Valued Member Points: 73 More actions · Answer 6

Try to use Server Compare.

The Server Compare application provides a simple and quick way to compare and synchronize two SQL server’s instances. You can compare server configuration parameters and other server objects such as logins, jobs, linked servers, and DTS packages. The application generates the T-SQL synchronization script based on the compare results, and can be run immediately, opened in Toad, or saved to disk. A history of all synchronizations with detailed reports and rollback scripts is available in this tool.

Supported servers: MS SQL Server 7.0, 2000, 2005 beta (Yukon).

http://www.toadsoft.com/ServerCompareBeta.zip

http://www.toadsoft.com/ServerCompareDemo.exe (flash demo)