SSIS - how to set ServerName and DatabaseName at run time inside FOREACH LOOP?

Hot Topic

multiple instances of Reporting Services - is it possible?
Optional Characters in Search String
SQLSERVERAGENT not starting (urgent please)
Regenerating Package ID programmatically
How can I run SQL 9.0
how to use CASE or IF in SSIS
Flat File created but with too many spaces
Need help constructing a view!
Server Error in '/WebVisual2' Application
Failed to decrypt XML node "DTS:Password"

SQL Server

Cant insert and delete
Newbie: backup problem
how to set the default filename when exporting the report
noob syntax question
How to access different sql server with quey analyzer?
Seperate SQL 2K Publisher, SQL2K Distributor, SQL2K Subscriber
Error in calculating Twelve Months to date using Time Intelligence
SQL 2005, multiple instances - what about ports?
Cannot open database
Souce and Dest auto-mapping

SSIS - how to set ServerName and DatabaseName at run time inside FOREACH LOOP?

I am new to SSIS world, so my question is very basic.

Setup:

In a company I work for we have 12 SQL servers each running between 1 and 3 databases with anywhere between 10 to 20 tables. I need to query some of these tables and merge results to the destination database.

The list of all these tables is stored in the separate table <SOURCES> of the following format [ServerName,DatabaseName,TableName]. Tables of my interest have identical structure (same columns) accross servers and databases.

Question:

How can I loop over servers and databases specified in <SOURCES> to run otherwise identical query against these tables

I can easily retrieve [ServerName,DatabaseName,TableName] from <SOURCES> as string variables using FOREACH loop. The problem is now - how do I use string variables to set up Server, Database and Table name at run-time

Thank you

 

 

 

 

 

 

Answer this question

SSIS - how to set ServerName and DatabaseName at run time inside FOREACH LOOP?

  • zhonglixunni

    I've got a sample chapter in PDF format posted here that walks through a tutorial on how to do just that. It's about mid-way through the chapter. Hope it helps:

    http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2005/12/30/58.aspx

    -- Brian



  • Philip Lee

    From the For Each Loop pass the server and database values into variables. You can then use the variables in expressions on the connection. Select the connection, and in the properties window click Expressions, expend and add a new expression onto the properties you require, or just the connection string property itself. The expression can reference the variables.

  • SSIS - how to set ServerName and DatabaseName at run time inside FOREACH LOOP?
Answer this question