siddharth.monani (8/13/2010)

---

Hi,

I am trying to create a package where I need to fetch server name information from one database (HQ) and then connect to all of those servers (Branch offices - BR) in a loop to copy certain data.

To do this, I am fetching the Server name information in an user object variable using an Execute SQL Task.
Then, I am looping through all the entries in the object variable using a For Each loop and assigning server name to another string user variable (say sServerName). Inside this loop is the data flow task for copying data.

I am using expressions to set the 'ServerName' property of my connection manager from the sServerName variable. This works beautifully at design time and the value is reflected in connection information as i change the variable value.

However, during runtime, the variable value keeps on changing however the connection manager only uses the first value everytime instead of getting the new value from the variable. I have set DelayedValidation to true and RetainSameConnection to false.

-Sid

Baskar B.V (8/16/2010)

---

I hope this dynamic mapping would not help inside data flow task. Because even if you map server name to the connection at run-time you can't do the column mapping dynamically between source & target in the data flow task.

You may need to try to achieve this in your control flow using execute sql task based on your need.

Raunak Jhawar (8/16/2010)

---

Siddharth,

Have you considered using SMO approach.
This could be tedious as you will land up designing the entire component itself.

Raunak Jhawar (8/16/2010)

---

Siddharth,
Foreach with one scipt task and EST. Loop parameters ki value...name of server will change upon some condition....now we have a server name...script task and EST...easy...but remember to set DelayValidation to true....