August 13, 2010 at 6:45 pm
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
August 14, 2010 at 1:10 am
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.
August 14, 2010 at 4:57 am
Thanks Lynn
August 16, 2010 at 12:13 am
Siddharth,
Have you considered using SMO approach.
This could be tedious as you will land up designing the entire component itself.
Raunak J
August 16, 2010 at 10:39 am
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.
[font="Arial"]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.[/font]
August 16, 2010 at 11:31 am
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
August 16, 2010 at 11:33 am
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.
August 16, 2010 at 11:51 am
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....
Raunak J
August 16, 2010 at 12:44 pm
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
August 16, 2010 at 11:21 pm
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.
jp
August 16, 2010 at 11:25 pm
This will lead to dependancy...don't you think so!!!;-)
Raunak J
August 17, 2010 at 11:44 am
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.
Use the same process but have the expression build/set the connection string property instead of just the ServerName property.
Worked for me... 🙂
Arkware
August 17, 2010 at 5:48 pm
not so much dependency as you might think.
If you have a standard set of variables ( I tend to limit it to database connections for example)
having that drawn from parent controller means that package can be used in multiple locations more easily rather than less...
I think of it along the same lines as a software contract. 😉
jp
August 18, 2010 at 4:58 am
Thanks Guys,
I tried the script approach and it worked :-), but :ermm: it seemed more like a workaround rather than the real solution.
The DMO was too long a shot so I didn't work on it 🙁
jmplun, Your approach was the one that was previously used but it was unnecessarily creating two packages, and that was the reason why I wanted to do it in one package itself.
I couldn't test this on the actual servers (production you konw) before I ensured the working and I didn't have multiple servers so the way I was trying to find which server it connects was by showing the connection string of connection in a message box. However, I got 2 VMs up as servers and did the testing and it worked!! :w00t:
Then later I came to know that the entire thing was working from the beggening itself. The only thing is, if you fetch the connection string information, it will still show the default value (the one at design time) and not the actual value at run time, even though it was connecting to the right machine.
But I did get some good ideas from this post.
Thank you all for helping out....
Cheers,
Sid
August 18, 2010 at 5:02 am
Happy to help....
Whose on facebook...on a lighter note...:-D:-D:-D
Raunak J
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply