SQL Server Performance Forum – Threads Archive

Moving DTS Jobs to another Server

I have moved DTS Jobs from one Server to another, and now need to update all Connections to use the New Server Name. What is the best way to do this other than opening each one and changing the properties of each connection?

I don’t think that there is a way other than open each one and changing the properties of each connection… Cheers
Twan

I have got reference in certain articles that the connection properties can be saved in separate data link files instead of hardcoding it in the dts packages . So while moving from one server to another the connection properties in this file can be modified . Some also say that the dynamic properties task can be uded for this . I am trying it but without much success as of now . I am still in search of an article which discuss these topics in a bit more detail . Pranab

Although its too late for you now, since your packages are already created, but its possible to setup dts packages to use global variables for servers and then you only have to change those variables on a different server, or have a process that looks them up somewhere. You can check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_elemtsk3_6q7f.asp
and
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_addf_misc_68tv.asp
Also Database Journal has a series of articles on DTS (with more to come) that may be helpful
http://www.databasejournal.com/features/mssql/article.php/3116201 Part 5 includes this comment that includes the line ‘You can control practically any DTS component – such as connections’ "An alternative approach to scripting, when dealing with automating changes to package parameters, involves the use of Dynamic Properties task. Dynamic Properties task allows such modifications at run time, but is configurable via a graphical interface. You can control practically any DTS component – such as connections, tasks, steps (we will talk more about steps once we start discussing workflow component of DTS packages), and global variables. Values of properties for each of these components can be derived from one of the following sources"
I’ve never done this, but have been told its possible. I’d check out the database Journal article series for more information. I’m going to, just to see if I can use some of this in the future to keep from having the same problem. It also sounds like a good way to be able to create a package in dev and move it qa and then production without having to save each time. Chris

There’s a number of ways to do this. Using "disconnected edit" in the DTS package is the best manual approach as you change values directly. Once you’ve got the hang of that using the dynamic properties to task to modify the values makes a lot more sense. You could also use the Client Network Utility (normally have a shortcut to this on the start menu under SQl Server) to create an alias for a server. If you then use that alias within your packages you just have to setup the alias on each server to point to itself.

]]>