For Each across list of databases

I need to get records from multiple databases.

In my main database, I have a list of databases related to seperate business units.

For each of those databases, I need to get a list of values from a table (the table exists in each database).

Basically

foreach database in a list
Do a Lookup
end

Possible?

The option I would consider would be to put a Data Flow Task in the PreExecute Event Handler of the For Each Loop Task to loop through the databases you need to access.

First, parameterize the Connection String of your source main Data Flow DB source (not the Event Handler DB source) with a variable. Here's an example of what I'm doing within an expression for the ConnectionString:

"Data Source=" + @.[User::masterServer] + ";Initial Catalog=" + @.[User::masterDatabase] + ";Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

Create a PreExecute Event Handler Data Flow Task in which you select the database names from sysdatabases with a OLE DB source (not the same source you're using in the main Handler Data Flow) connected to the server and write it to a RecordSet Destination. You'll have to define a new variable of type Object as the RS destination.

Then, specify an ADO Enumerator as the Enumerator of your For Each Loop and map the database name to the variable that you're using to parameterize the Connection String of your non-Event Handler from the RecordSet variable.

|||Thanks!

That looks like exactly what I'd like to do.

There are several details in your response that I'm not sure about. But I'm going to try to work them out on my own before asking for more help.|||

Dear mr_superlove,

I have been looking to use For Each Across a list of database. Your reply seems very helpful..

I am new to SSIS. I tried to do what you said. I am missing something to get it working. Can you elaborate the procedure you wrote in step by step way.

I would really appreciate you effort.

Thank you,

Sanjeev