Click here to Skip to main content
15,920,801 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
There are 4 Connection strings with different SQL Servers (which I set up in SSIS Connection Managers section): Database name is same in all the servers:
SERVER DATABASE

1. dbTestServer dbFees (Main Server and Database)
2. dbTestServer1 dbFees1
3. dbTestServer2 dbFees1
4. dbTestServer3 dbFees1

dbTestServer is the OLEDB Source and other Servers are OLEDB Destination that needs to be updated everytime we run package. Now, I want to take data from dbTestServer-dbFees and copy to all the other databases. I created a Dataflow task to copy data from dbTestServer to dbTestServer1.

But I need to put this data flow task inside ForEach Loop container to change the connection/Server dynamically so that it will work like:

1. First run- By default OLEDB Source is set to dbTestServer and OLEDB Destination is set to dbServer1 and data is copied from dbFees to dbFees1.
2. Second run- OLEDB Source is set to dbTestServer and OLEDB Destination is set to dbServer2 and data is copied from dbFees to dbFees1
3. Third run- OLEDB Source is set to dbTestServer and OLEDB Destination is set to dbServer3 and data is copied from dbFees to dbFees1.

I need step by step solution as I am new to SSIS packages and I tried multiple solutions but NOTHING worked so far!

Appreciate your help!

What I have tried:

I created a Dataflow task to copy data from dbTestServer to dbTestServer1
Posted
Comments
[no name] 2-Jun-21 15:27pm    
Get it working for one "source" first; note the place where you "define/set" the source.
smchat 2-Jun-21 16:00pm    
Gerry: It is working for one source and destination.
Source and Destination are defined in "Connection Managers" Section in SSIS package.
I need to know how to use For Loop or ForEach Loop Container to loop through all the different destination servers.
[no name] 3-Jun-21 12:20pm    
https://stackoverflow.com/questions/19516253/overriding-ssis-configuration-connection-string-in-net

https://kamakshisuram.wordpress.com/working-with-ssas/ssas-connection-string-dynamically/

https://www.sqlchick.com/entries/2015/1/4/parameterizing-connections-and-values-at-runtime-using-ssis-environment-variables

etc.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900