SSIS ForEach Loop Container - How to dynamically change OLEDB Destination connection at run time

Question

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!

Thank you

Answer 1

I suggest using FOR LOOP.

My logic is to increment variable on each loop and create an expression with a connection string and a number of iteration.

1st step is to create Connection Manager with server name dbTestServer1 and database name dbFees1

2nd step will be to add a connection manager to OLE DB Destination

3rd step is to create 2 variables: ConnString and Iteration. For Iteration default value set to 1, because you need dbTestServer 1 ConnString you need to set like your initial connection string, just on place 1 in dbTestServer1 to set (DT_STR, 1, 65001)@[User::Iteration].

Like on next 2 pictures:

When you set variables, you need to set expression in OLE DB Connection Manager.

From drop-down select connection string and type @[User::ConnString].

And finally set FOR LOOP like on picture

NOTE: I can't test package because I don't have server names like you, but this is logic of how to solve your problem. And this is only solution for what you asked, you must create whole package on your own.

For main server and database, just add one OLE DB Source with static names for server name and database name. And you don't need script task if you using my logic.

Answer 2

Here is the code I have used to dynamically change connection server/database inside C# Script task in SSIS:

Variables I pass to the C# Script task under ReadOnlyVariables: (set these up in your Variables inside SSIS)

User::DatabaseListOnThisLoop_ConnectionString
User::DatabaseListOnThisLoop_DatabaseName

This is the name of the connection string I am dynamically change that is in my ConnectionMangers in SSIS:

SourceServerDBForClassification_Dynamic

FULL SCRIPT from my C# Script task inside SSIS. As long as you setup the variables and put the 2 in above in the ReadOnly section of the script task, you should be able to just copy/paste the entire code below into your C# Script task.

NOTE: The Namespace may give you an issue so may want to keep the one that is generated in your code when adding the script task.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;  // dont think this is needed, I used this for message box for some testing, but leaving here just in case

namespace ST_f8d6dad17af541bbb0010c9fce3ccbb0
{

    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        public void Main()
        {
            // get connection string from variable
            
            string ServerConnectionStringOnThisLoop = Dts.Variables["DatabaseListOnThisLoop_ConnectionString"].Value.ToString();
            string DatabaseOnThisLoop = Dts.Variables["DatabaseListOnThisLoop_DatabaseName"].Value.ToString();

            // this could change depend on what type of connection you are using for provider and other settings
            string DynamicConnectionString = "Data Source=" + ServerConnectionStringOnThisLoop + ";Initial Catalog=" + DatabaseOnThisLoop + ";Provider=SQLNCLI11.1;Integrated Security=SSPI;";
            

            // Add the OLE DB connection manager set to existing connection
            ConnectionManager SourceServerDBForClassification_Dynamic = Dts.Connections["SourceServerDBForClassification_Dynamic"];
            

            // now set the dynamic connection above to the connection string passed in from SSIS package
            SourceServerDBForClassification_Dynamic.ConnectionString = DynamicConnectionString;
            

            // now set the package connection to the one we just created from using the variable from the SSIS package
            Dts.Connections["SourceServerDBForClassification_Dynamic"].ConnectionString = SourceServerDBForClassification_Dynamic.ConnectionString;
            
            Dts.TaskResult = (int)ScriptResults.Success;
        }


        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };


    }
}