Hi,
I have to transfer DTS packages from one server to
another. What is the best way to do it. I have never
done it before. Please help me with this.

Thanks

Steve

"Steve" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Transferring DTS Packages
(http://www.sqldts.com/default.aspx?204)


--
Darren Green
http://www.sqldts.com

If you don't have too many to move, you can use 'Save As'
functionality to move DTS pkgs from one MS-SQL instance to
another (you just need the proper authentication
credentials on both SQL instances).

An approach that could work for *many* packages is to
backup 'msdb' from the source server & restore it on the
destination server . . . as all DTS pkgs are stored
in 'msdb'. Both SQL instances need to be exec-ing the
same version of MS-SQL, etc. Of course, this method will
bring over more that just DTS pkgs (server logins &
scheduled jobs, along with other stuff). And if you're
not moving 'master' as well (which tracks DBs) this
approach could be problematic.

A more surgical approach is based on the fact that Local
Packages, as opposed to Repository packages, are stored in
the sysdtspackages table on the msdb database. So it
follows that you could transfer packages between servers
by transferring data between the respective sysdtspackages
tables. I haven't tried this method personally, but it
seems logical.


Another approach are 3rd party (non-MS) tools like those
described here
<http://www.swynk.com/friends/green/DTSBackupVB.asp> &
here
<http://www.databasejournal.com/featu...l/article.php/
1461521>. Again, I haven't used these personally, but it
may be worth a look.


Good luck!

-DMllp/DBA