July 10, 2008 at 9:32 am
I have 3 servers I'll refer to as server 1,2,and 3. I have a main ssis package and many ssis packages that the main ssis package will execute. I will be storing in a package variable which of my 3 servers I need to connect to before the main ssis package starts executing the the child packages.
We use config files that contain the server name, database name, and all other info needed to connect.
There are a number of ways I know to accomplish this. The easiest in my mind was to have 3 sets of config files and execute xp_cmdshell to copy the correct files to the location where each child package will look and so the main (parent) package could run the correct command based on the value in the package variable and move the correct files to the correct folder before the child packages start. Well that option is shut off for the SQL Server and my DBA doesn't want to activate it.
Another way would be to use the script task and change the server name on the fly after the name is fetched from the config file. So far I haven't been able to get that to work. If someone knows how to make it work and can help me figure it out that would be good.
Is there a way for me to make the main (parent) package change each of the child packages so that they will look in one of 3 folders for the correct config file? Seems to me this would be a simple way to make this work if it is possible.
And the last way I know which will take a bit of time to set up and maintain is to store the entire config file in the database line by line and then build it each time before execution with the correct server name.
Do any of you know a better way? If not do any of you know how I can change where a package looks for its config file before the package is executed? Meaning the parent package would make that change and then immediately call the child package? Or is there a way to make the child package use a package variable to tell it where to find its config file?
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
July 10, 2008 at 9:59 am
In package configurations, you have an option to set a value based on a parent package variable. So, if you use your parent package's config file to get the server name and put it into a variable, using the parent package variable option, you can configure the connection for the child package.
July 10, 2008 at 10:25 am
I think I have found what you are talking about.
I select configuration type as parent package variable and then tell it to put whatever is in that package variable into the connection string.
Thanks, I am exploring this now!
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply