0

I have a SSIS-project which uses xml-configuration file (dtsConfig) where the connection string to source data base is given. Configuration file is stored to environmental variable.

Data needs to pulled from four different data bases, i.e. now I need to run the same set of packages four times by using four different connection strings.

I can make four different configuration files where each of them has a different connection string and update it to the environmental variable after each run. This is how I'm doing it now and it works ok, but I wouldn't like to keep on updating the env variable all the time.

Or then I can use the same configuration file and just update the connection string after each run. But I think it's even worse idea than having four different files.

What I would like to do is dynamically change the connection string after each run.

I have a master-package which runs the set of packages I want. So I was thinking of just adding this master package four times in the control flow, after each run I'd need to update the connection string which then would be used at the next run. But how to actually do this?

Or for each loop container which would contain the master package and would loop the it four times and changing the connection string after each iteration would be cool as well.

Juha S
  • 25
  • 1
  • 7

1 Answers1

0

To run the packages sequentially, you could simply create a table or file with the connection strings (eg. 4 rows for the 4 data sources). You would then have a for each loop which would loop through the connections (from the table or file) and call the child package passing the connection string down to it as a variable. The child package would access the variable through a Package Configuration. The variable in the child package would be pointed to the connection string of the connection.

Ali_Abadani
  • 725
  • 3
  • 11
  • 24