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

Switching Source Connections Before/During Package Execution Expand / Collapse
Author
Message
Posted Thursday, July 10, 2008 9:32 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
SQL Programmer
Ipreo
Post #531734
Posted Thursday, July 10, 2008 9:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
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.
Post #531755
Posted Thursday, July 10, 2008 10:25 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
SQL Programmer
Ipreo
Post #531784
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse