Copying a DTS package to another server 1

[bessebo]

I have a server which contains a number of DTS local packages that I want to move to another server. I don't want to restore the whole msdb database to the new server because I already have a number of SQLServer jobs created. Is there a way to move DTS packages from one server to another without having to restore the MSDB database?

Thanks in advance,
Bessebo

 

[tlbroadbent]

Check the following article from SQL Mag online.

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=7850

Or these recommendations from a SQL FAQ.

Q. How can I transfer DTS packages from one SQL 7 server to another?

A. Three choices :-

1. Use the DTS transfer wizard. Set msdb as the source and destination databases and "select * from sysdtspackages" on the source.

2. Save each DTS package as a file. Copy the .dts files created to the target machine. From EM, right click Data Transformation Packages and choose all tasks Open Package for each package.

3. Open them in the package designer - choose save as, and save them to the other server.

Option 1 is the quickest for lots of packages Terry

http://members.home.net/tlbroadbent/index.htm

 

[foxdev]

If both servers can be seen from a single machine, you can crank up Enterprise Manager, open the DTS package for design, and choose Save As from the Package menu. The Save As dialog will allow you to save to a different server. Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com

 

[bessebo]

Terry,
I tried to use the DTS Export wizard, I chose the msdb database for source and destination but the system tables will not show up. Do I have to use the query option and enter 'select * from sysdtspackages'? Will this do the trick. There are about 30 packages and I'd rather use option 1 than either of the other options.

Thanks,
Bessebo

 

[tlbroadbent]

I had not tried to use DTS to copy DTS packages in mass previously. I found the information and shared it here. Since then, I've tried and cannot get it to work because the tables are not visible. Some workarounds have been proposed in various forums, none of which worked for me.

Therefore, I decided to use a tried and true, simplified method. I linked the second server to the first and simply inserted the records into sysdtspackages on the second server.

This is my query:

Insert server2.msdb.dbo.sysdtspackages
Select * From msdb.dbo.sysdtspackages

Worked great! Hope this is more helpful. Terry

http://members.home.net/tlbroadbent/index.htm

 

[bessebo]

Thanks Terry,
After I linked my servers I had to use the sp_addlinkedsrvlogin stored procedure to set the sa password when it logs into the remote server since I kept getting a sa login failed error. After that I ran your query and it worked like a charm.

Thanks!
Bessebo