SSIS foreach loop container - how to dynamically change OLEDB destination connection at run time

Question

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 2-Jun-21 9:22am

smchat

Add a Solution

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.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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