SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamically changing Server info for OleDB Connection


Dynamically changing Server info for OleDB Connection

Author
Message
siddharth.monani
siddharth.monani
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 32
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.

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.

-Sid
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40940 Visits: 38567
At my previous employer we used a script task to set the connection dynamically in our SSIS packages after using a SQL task to query a configuration table. We did it in a sequence container as we didn't need to loop through servers in our setup.

You should be able to do this in your FOR LOOP (?) container as well. Unfortunately, I don't have access to the code we used anymore, but I'll look around and see if I happen to have a printed copy of the code available. Itry to keep copies of generic code in case I need something similar some where else.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
siddharth.monani
siddharth.monani
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 32
Thanks Lynn
Raunak Jhawar
Raunak Jhawar
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1683 Visits: 1944
Siddharth,

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

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Baskar B.V
Baskar B.V
SSC Veteran
SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)

Group: General Forum Members
Points: 263 Visits: 341
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


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.

BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.

siddharth.monani
siddharth.monani
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 32
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.


Thanks Baskar.

The structure across all the databases on different servers will be the same, so that is not a concern.

-Sid
siddharth.monani
siddharth.monani
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 32
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.


Not really because that's a long shot but thanks for suggesting. I might have a group of these packages to develop in near future and this is one good idea I could use for them.
Raunak Jhawar
Raunak Jhawar
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1683 Visits: 1944
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....

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
siddharth.monani
siddharth.monani
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 32
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....


Thanks Raunak, this seems like a good idea. I'll try it out.

-Sid
jmplun
jmplun
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 38
As Lyn suggested you can do this by changing the value in a script.
Another way I have handled this (without using script) is to call a child package where it inherits the value of the server/connection string from the parent package.

I tend to this sort of Process Controller Package, and Child Process package quite a bit as it gives some structural separation between different packages and can give a lot of flexibility.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search