Is there an easier way of changing the connection strings for 350 DTS''s?
-
Can you change the connection properties of a DTS to new server, new user id and password when moving to another server?
I have about 350+ DTS's and I am trying to find a quicker way of changing the connection strings than opening each one and changing them manually.
Does anyone have any ideas?
-
There's no easy way to do it if the connection information is hard-coded in each package. If you've used dyanamic properties tasks and UDL or .ini files, you can change the connection info in the file after moving the package.
There are lots of posts and a few articles on this site that describe how to save packages as VB, make a global change, and re-create the packages on the new server. Try searching for "changing connection in all dts packages". There is also good information about making portable DTS packages on http://www.sqldts.com.
Greg
Greg
-
I wrote this script to document the connection properties. You could use it as a base to modify the properties.
http://www.sqlserver.org.au/resources/ViewResource.aspx?resourceId=40
--------------------
Colt 45 - the original point and click interface
-
Thanks for your help guys. I've tried a few of these now.
Btw, Phill, it is great that I can see which connection is using what details in each DTS. How can I use this to update all these connections in the DTS's?
-
By using DTS to transfer packages as described here http://www.sqldts.com/default.aspx?204 and here http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=308692 you can change connections while transferring them.
Greg
Greg
-
The script should give you an idea of what objects and properties you need to update. All you need to do is adapt the script to your needs.
--------------------
Colt 45 - the original point and click interface -
Phil, Not able to download your scripts....
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply