SQL Server Performance Forum – Threads Archive

Changing Source/Destination Server in DTS Package

Is there any way we could dynamically change the source and destination servers in a package when deployed across different servers. Lets suppose I have made a package that transfers data from Server A to Server B. Now I am sending it to client, may be there the two servers are C and D. so how can we dynamically replace A with C and B with D, without manually changing it by opening the package in design mode.

I’m not sure whether its possible thru the Activex or vb script, I think only by using DTS designer only it is possible. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi, You can define global variables in DTS package using the Dynamic Task properties. Assign these global variables to the DTS tasks. These global variables can be populated from ini file. So the client just have go and edit the ini file.
Visit this link for more info
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=21587&DisplayTab=Article

How could I change the destination dynamically in a data transform? My source query depends on a global variable, and I want to populate the columns from the source in the destination. How could I achieve this? Any help is appreciated.

http://databasejournal.com/features/mssql/article.php/1461481 Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks for the response. But this link only addresses to changing the source(I have finished this part). I want to change the DESTINATION now and am having problems with it. How do I do this?

CHange the connection source variable to accomplish, I beleive Dinesh can give you more insight in this regard. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I could change the DESTINATION vaiable, but the problem occurs in the data transform. The source-destination mappings are no longer valid and a copy cannot take place. Is there a way of dynamically generating the source-destination mappings in a data transform??

Well, with a lot of research. I have concluded that the destination table cannot be changed on the fly.

You could create the same table (or rename) on all the relevant databases…

quote:Originally posted by ndoshi Well, with a lot of research. I have concluded that the destination table cannot be changed on the fly.

I successfully change the Destination table in many of my packages. The key is to set a global variable to the table name only (leave off the "[database].[owner]." values), then use a Dynamic Task to find the Destination property of the correct Task, double-click on it, and set it to the global variable. In addition to the ability to set the value using an INI file, you can also set it via a query, data file, or environment variable, but I find the global variable works fine for me. Jim

]]>