Deploying a DTS package

Question

Deploying a DTS package

THNQdigital

SSC Eights!

Points: 849
More actions
May 2, 2005 at 2:34 pm

#60836

Hi,
I have DTS package ( SQL 2k) on the test server.
I need to deploy the same on to PRO server.
I am planning to save the package as Strutured storage file on the disk and then copy the file (.dts) file to PRO and open the packge in DTS ( PRO SQL Server). is this correct method.. ? Please let me know.
Thanks
THNQDigital

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

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

philcart SSC-Forever Points: 47794 More actions · Answer 1

The "correct" method would depend on your operating environment and what sort of change control process you follow.

The method you've outlined will work, but bear in mind that unless you've setup the DTS pacakge to dynamically assign connection properties you will need to edit the package on the PRO server to correct them.

Also, if you use SQL Server package logging, make sure you're pointing to the right server.

--------------------
Colt 45 - the original point and click interface

swede SSCommitted Points: 1773 More actions · Answer 2

Another way is to open the package on the your test server and save it onto your production server with the save as.

But when you have many DTSs to copy over to the production server, I use a DTS to copy the DTS from sysdtspackages. The following select will select only the most current version of the package:

select t1.*

from msdb..sysdtspackages as t1

inner join (select name, id, max(createdate) as createdate

from msdb..sysdtspackages

group by name, id) as t2

on t1.id = t2.id

and t1.createdate = t2.createdate

and t1.name like 'dts5%' <--- change this to your name prefix

order by t1.name

provost SSC Enthusiast Points: 142 More actions · Answer 3

There is a handy freeware tool on http://www.sqldts.com called DTSBackup 2000 that lets you select the DTS packages on the source and copy them to a destination server.

THNQdigital SSC Eights! Points: 849 More actions · Answer 4

Thank you all for your time.. For now i will stick to the methods i described, will certainly have to edit the connection properties in the destinations server as rightly pointed out by Phill Carter.. neverthelss thank you all for your valueable sugestions.. i can use them as appropriate..

Thanks

THNQdigital

Roy Ashbrook SSChasing Mays Points: 632 More actions · Answer 5

re: copying the package to another server, you could use the dts.package com object to load the package and save it to the target server if you just need to copy it.