Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Dynamically changing Server info for OleDB Connection Expand / Collapse
Author
Message
Posted Friday, August 13, 2010 6:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 5, 2011 9:22 PM
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
Post #969326
Posted Saturday, August 14, 2010 1:10 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 20,799, Visits: 32,717
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.




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)
Post #969365
Posted Saturday, August 14, 2010 4:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 5, 2011 9:22 PM
Points: 9, Visits: 32
Thanks Lynn
Post #969380
Posted Monday, August 16, 2010 12:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:28 AM
Points: 1,044, Visits: 1,839
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
Post #969560
Posted Monday, August 16, 2010 10:39 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 3, 2011 7:44 AM
Points: 151, 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.
Post #969930
Posted Monday, August 16, 2010 11:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 5, 2011 9:22 PM
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
Post #969947
Posted Monday, August 16, 2010 11:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 5, 2011 9:22 PM
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.
Post #969949
Posted Monday, August 16, 2010 11:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:28 AM
Points: 1,044, Visits: 1,839
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
Post #969954
Posted Monday, August 16, 2010 12:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 5, 2011 9:22 PM
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
Post #969971
Posted Monday, August 16, 2010 11:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 1, 2011 10:08 PM
Points: 20, 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.
Post #970147
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse