globally changing DTS Connections across instance?

Question

globally changing DTS Connections across instance?

Rich Hansell

SSCrazy

Points: 2346
More actions
June 7, 2007 at 2:47 pm

#70188

Ladies and Gents.. I have >240 DTS packages that need their connection settings changed to match their new environments. One of the other DBAs wrote a vb app that changes them however it sets all of them to automap - a nightmare to readajust on large packages. Does anyone know of a way to globally replace the servernames in the DTS packages without any real side effects?
TIA

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

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

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

The layout and annotations in DTS are not part of the object model. Therefore any modifications done outside the Designer GUI will cause them to be lost.

May I suggest that you setup the packages so they don't need to be changed for an environment change ...

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

Obviously, you did not use UDL files for your connections. Unfortunately, the only way to do this globally is a VB app like you have created and you cannot get around the issue you have with it.

For a quick solution, I would suggest you use the SQL client to create an alias for your old server and point it at the new one. This does mean you will have to deal with the old server name still appearing and confusing people until the end of time, but it will get you past the current issue.

After that, I think you are simply going to have to have someone spend the time to go through all of them and make the modification manually.

Daniel Wolford SSC-Addicted Points: 451 More actions · Answer 3

You could just rename your new server to match the old one ... just kidding!

I agree with Michael Earl, that the best quick solution is either an alias to the old server or putting a hosts entry in that accomplishes the same thing.

I am curious, though, you mentioned that there is a vb app that currently can edit each individual package in question? If that's true, and it's an app that runs outside of the dts packages (so the vb app opens each package, modifies it, and saves it), then it might be possible to add the code you need to the vb app.

Paul Rubel Ten Centuries Points: 1047 More actions · Answer 4

Script the packages. Perform a Global replace on the server name and then run the scripts on the new server. You'll still have to update a column in a table in MSDB. The column contains the servername. It's been a while since I had to do this but I was able to automate it.