February 16, 2015 at 1:35 am
Hi,
I don't have much experience with SSIS so apologies if I make no sense.
I have a scenario where my central server has to gather data from few instances of SQL Express on the network and be able to send some configuration data as well. I’ve made a simple dtsx package where I have a sequence container with all I need to synchronize one instance. I don’t really want to copy/paste same container for each sql express instance running and change connection string because the configuration may change and if one of them will be disconnected from the network the whole package won’t execute. I was wondering if we can work something out by having a list of servers, for each server on the list change the connection dynamically and run the sequence container.
Appreciate all your help and hope I made kinda clear what I would like to do.
Thanks!
February 16, 2015 at 2:34 am
You can use a for each loop to loop over your instances.
At each iteration you'll change the connection manager used in the sequence container with an expression.
More info:
Dynamic Database Connection using SSIS ForEach Loop Container
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 16, 2015 at 3:03 am
It looks like something i'm looking for! I'll give it a try and will let you know about the outcome. Thank you.
February 16, 2015 at 5:27 am
Thank you for help, it's working now!
In the article you've provided there is one thing i believe is missing, I couldn't find the step that would actually change the connection string variable but i found out somewhere else that you have to use Variable Mappings in the for each loop to assign new value to the variable. one more thing i had to do is set DelayValidation to TRUE on my container otherwise i would get an error message at the start that connection string for the dynamic connection is not valid.
February 16, 2015 at 5:40 am
Emil Bialobrzeski (2/16/2015)
Thank you for help, it's working now!In the article you've provided there is one thing i believe is missing, I couldn't find the step that would actually change the connection string variable but i found out somewhere else that you have to use Variable Mappings in the for each loop to assign new value to the variable. one more thing i had to do is set DelayValidation to TRUE on my container otherwise i would get an error message at the start that connection string for the dynamic connection is not valid.
OK, great that you got it working.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply