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


Switching Source Connections Before/During Package Execution


Switching Source Connections Before/During Package Execution

Author
Message
Robert W Marda
Robert W Marda
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4459 Visits: 113
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
Michael Earl-395764
Michael Earl-395764
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21745 Visits: 23078
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.
Robert W Marda
Robert W Marda
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4459 Visits: 113
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
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