SQL Server Performance

Changing Source/Destination Server in DTS Package

Discussion in 'SQL Server DTS-Related Questions' started by bridge, May 27, 2005.

  1. bridge New Member

    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.
  2. satya Moderator

    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.
  3. DBA_Newbie New Member

  4. ndoshi New Member

    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.
  5. satya Moderator

    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.
  6. ndoshi New Member

    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?
  7. satya Moderator

    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.
  8. ndoshi New Member

    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??
  9. ndoshi New Member

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

    You could create the same table (or rename) on all the relevant databases...
  11. jburgess New Member

    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

Share This Page