CODEWITHSUNDEEP
sql-serverdts
Elias Haileselassie
Elias Haileselassie

Reputation: 1395

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

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 Redgate's 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.

Upvotes: 3

Views: 8838

Answers (4)

Ed Harper
Ed Harper

Reputation: 21505

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.

Upvotes: 3

gbn
gbn

Reputation: 432631

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...

Upvotes: 3

Matt Spradley
Matt Spradley

Reputation: 8444

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.

Upvotes: 1

Paulo Santos
Paulo Santos

Reputation: 11587

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.

Upvotes: 4

Related Questions