SSIS - Dynamically loop over multiple databases

Question

I have to consolidate data from from 1000+ databases having the same structure/tables in one unique DB. DBs may be added and removed potentially on a daily basis so I need to retrieve the list of DBs dynamically and run the dynamically generated SQL query to extract data on each of them.

I designed the Data Flow with a query from a variable that is working fine if executed with a static value:

With a SQL task I get the list of instances, I loop over the them and with a nested Foreach Loop/SQL task I retrieve the database names and create the dynamic SQL with the following statement (DB name is anonymized):

SELECT 'select ''' + name + ''' as DatabaseName, ID from ' + name + '.[dbo].[Orders] as querytext FROM sys.databases WHERE name LIKE ( 'XXX%_%' );

This part is also working fine:

How can I use the result of the SQL task "Execute SQL Task - Get query text" as query to be executed in the Source "OLE DB Source 1" (part of "Data Flow Task 3")?

I tried mapping an Object variable "User::SqlCommandFromSQLTask" in the result set of the SQL task, then set it up as ADO object source variable and with a Script task convert it to string and pass the value to the variable SqlStringFromSQLTask3 (used as source in "OLE DB Source 1") but I get the error Violation of PRIMARY KEY constraint, like if the data flow is always running with a static value I set up as default:

While, if I remove the value from the variable panel, I get the error "Command text was not set for the command object.", even changing the property DelayValidation of the Data Flow to false.

Any help is much appreciated.

Answer 1

When I have used SSIS to connect to multiple SQL Server boxes, I have stored those SQL Server connection strings in a table in a central database. Then I use a query of that table as the input to the foreach loop data flow task. If we ever have to change a sql server connection string, which does happen, we just update that table with the newest value.