DTS server name

Question

DTS server name

Vichka

Ten Centuries

Points: 1218
More actions
September 14, 2005 at 9:24 am

#61031

I need to transfer DTS over to the new server. In some DTS the server name in connection properties set as LOCAL, and when it's transferred to ne new server it has no problem with another server name - it's again local, does not matter where you will run it.
In some packages the same is set as MyServer (server name itself) and when I transfer that DTS to another server it keep saying MyServer (but I need NewServer name)
Is there any place in the msdb (any table) which contains that server name in it. Then I can change it all at once. So far, I found DTS packagedata all encrypted, and nothing could be changed there.
The problem is to change it manually - we have tons of DTS and half of them has the server name instead of local.
Any ideas...?

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

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

Richard Kemp SSC Journeyman Points: 76 More actions · Answer 1

You've got the classic DTS oops - point-and-click ease, but poorly designed ETL. DTS (in SQL 2000) is great for putting together stuff quick, but it doesn't provide an automatic framework for portability.

You could do this most easily with VB or VBScript - do you have any VB savy people handy? They would have to get familiar with the DTS object, spin through the packages, and look for connection objects and change the appropriate property. I've done DTS automation before, but its not very straightforward in terms of how you navigate around the model. You would need an experienced developer, or at least someone very patient!

An alternative would be to do "Save As" for each package to a VB file. This would produce a vbscript text file for each package which you can edit with Notepad, and change the connection strings - or use "splat" or some other global change editor. The down side is that its a one-way street - you can't take you modified vbscript back into DTS designer. So you'll have functional vbscripts you can schedule, but you won't be able to modify them as DTS Packages anymore.

Sorry I couldn't come up with something better - good luck!

- Rick

DanKennedy Hall of Fame Points: 3736 More actions · Answer 2

Try adding a Dynamic Properties Task, create a Global Variable called ServerName and set the Data Source of each of the connections to read from the global variable. Make sure you also reference the global variable in scripts and don't hard code machine names.

Dan
www.firstcs.co.uk

Nick Hatt Valued Member Points: 62 More actions · Answer 3

We just went through this , in the end we saved over 150 DTS on the new server using 'save as' after changes to the connections were made.

We are now using a 'template' with each DTS which is created from a Dynamic Properties Task - which calls an .ini file, which inturn can store information like the new servername.

Not a fix now, but perhaps a fix for the future...

N Hatt

swede SSCommitted Points: 1773 More actions · Answer 4

We use data links for our connections and have not had any issues moving DTS from our developement server to our production server.

Jules Bui SSC Eights! Points: 857 More actions · Answer 5

Please refer to this article for dts portability

http://www.sqlservercentral.com/columnists/tdavid/moreportabledtspackages.asp

Jules

Jules Bui
IT Operations DBA
Backup and Restore Administrator

Vichka Ten Centuries Points: 1218 More actions · Answer 6

As I can see I am not alone with my pain

Thanks to everyone!

I modified DTS manually on the new server and we will be thinking of changing them, maybe to include the Dynamic Properties Task. I will try with VB files...just to see how I can change it.