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

for any button click event

Hi All,

I am new to sql server 2005.

I have developed an new report using sql server 2005

repoting services in Business intelligent Development studio

so any one can help me how to include this report

on any aspx page

ex for any button click event the report must be generated.

So please any one of you help me out.

Sandy,

From the FAQ's at: http://www.microsoft.com/sql/technologies/reporting/faq.mspx

Q. How can I add Reporting Services reports to my application?

A. Visual Studio 2005 (Standard and Enterprise editions) contains a set of freely redistributable Report Viewer controls that make it easy to embed Reporting Services functionality into custom applications. Two versions of the Report Viewer exist, one for rich Windows client applications and one for ASP.NET applications.

If you want more granular control, once the report is published to the report server you will notice that it has a URL. Simple link to that URL with a hyperlink, or, do a response.redirect or server.transfer on a button's click event. Once you have the full url to the report you can start playing with it to get the results you want. (i.e. Button 1 passes parameter(s) A, button2 passes parameter(s) B etc.)

For more guidence I suggest:

http://msdn2.microsoft.com/en-us/library/ms365308.aspx

http://www.microsoft.com/sql/technologies/reporting/default.mspx