Dynamic OLEDB Connections in SSIS

Question

I am designing a SSIS package which imports data from one data base to other database. In reality I need to import data from multiple data source to one destination database. One way to do, that I know is to use package configuration for all data sources (connection strings) and run multiple instances of the same package. But I want something like, I should provide as many connection strings as I need at a point of time in my config file and my package should connect to each database reading data source connection strings from configuration and imports to my destination table.

Is this possible in any way?

Answer 1

If your Data Flow Task is going to be the same for every data source (e.g. using same table from each data source), you could do something like this:

1. Create an object variable, say ConnStrList. This will hold the list of connection strings.
2. In a script task, loop through your config file and add each connection string to ConnStrList.
3. Add a ForEach loop container, set it's data source to ConnStrList. Create a string variable, say ConnStr. This will hold an individual connection string. Set ConnStr as the iteration variable of the foreach loop.
4. Add your Data Flow Task inside the ForEach loop container.
5. Create an OLEDB connection manager for your OLEDB source. Go to Properties -> Expressions and for ConnectionString, assign the variable ConnStr.

If the DFT is going to be different for each scenario, you might want to have separate data flows for each source.

Please let me know if this answers your question, or if I am getting the scenario wrong.