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.
Can you please help me with this? What do I need to do to get the value reflected in connection manager?
Thanks in advance for your help.