Providing database connection information while running SSIS packa

Todd C

2009-01-22 19:26:02 UTC

If I understand you correctly, you have 15 database with the same meta data
and you want to load them all using SSIS in some efficient and elegant
fassion.

If that is not correct, please advise.

If that is the scenario, then you will most likely need the following:
An Execute SQL task that queries a 'control' table somewhere that contains a
list of Servers/Databases that are your sources. This gets loaded into an
Object type variable using the results page of the SQL task.

From there, have a For Each loop that iterates over the records in the
Object table, assigning values to variables for ServerName and Database Name
using the mapping page.

Design your data flow inside the loop.

For the source Connection Manager, create an Expression for the Connection
String Property and in the Expression, utilize the Server and Database
variables accordingly.

Now, each time the loop reads a new record in the 'control' table, it will
assign a new ConnectionString to the Source Connection Manager and go through
the data flow.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Post by Mallikarjun
Hi,
I have developed the ETL, which is getting the data from one source
database. And it is working correctly.
Now my problem is i have 15+ source database with a same database structure,
my ETL should complete the the loding of data from each data bases.
I would like to know how to change the source database coneection
information after each sucessful completion of load, & run the same ETL for
next source database, till all the 15+ soruce database is completed.
Please help me
Thanks in advance
Malliakrjun