Operating on different source SQL Server instances in a single SSIS package

In some cases we need to do a single task for lots of SQL Server instances. Assume that we have a web based programme. The programme’s database is distributed across the country and we have 10 different virtual (VM) servers to host the programme’s databases. The programme is working based on some configurations that are stored in a CONFIG database. The CONFIG databases are hosted by 20 different SQL Server instances to serve 20 different clients. The SQL server instances are all named SQL server instances hosted by those 10 virtual servers. We need to update the CONFIG database for all regions on a monthly basis. The database structure of all CONFIG databases is the same. In this case a simple way is to create an SSIS package for each source server to collect the data from all source databases one-by-one. This means that we will have 10 copies of the same SSIS package that each package is pointing to a server as a source server. We need 10 packages because we can retrieve the CONFIG database list by writing a T-SQL script or using an extra Foreach Loop Container. So we need a SSIS package per server.

The other way is to create a dynamic solution to collect the data from all source databases hosted by different SQL server instances in a single SSIS package. In this case we need to have a list of source SQL server instances as a variable. Due to the fact that there is no array list variable type in SSIS, we need to make the solution work by converting a comma delimited string variable to an Object variable containing the list of servers. In this article we will represent a dynamic way to work with different SQL Server instances. Our goal is to retrieve the list of SQL Server instances coming from a comma delimited string variable. So we’ll have a string like “SQLSRV01\SQL2012,SQLSRV02\SQL2008,SQLSRV02\SQL2012,SQLSRV04” representing different SQL Server named instances

To achieve the goal of creating a dynamic solution, follow the process below:

1. Create a new SSIS project and name it “Dynamic Server Names”

2. Open the package

3. Create the following variables:

a. Servers; Data type: String. It is an input variable containing the SQL Server instance names that are comma delimited.

b. ServersList; Data type: Object. It stores the list of servers converted from the comma delimited string

c. ServerName; Data type: String. It contains each server name.

4. Add a script task to the control flow. We need to make our hands dirty here to convert the comma delimited string variable to an array list. The array list is going to be stored in the “ServersList” variable that is an object variable.

5. Place a Foreach Loop Container to the Control Flow

6. Place another Script Task to the Foreach Loop Container. It will show the server name as a message to see if the solution works fine. Actually, you can put every other tasks that you need in your case.

7. Type “SQLSRV01\SQL2012,SQLSRV02\SQL2008,SQLSRV02\SQL2012,SQLSRV04” as an input value for the “Servers” variable

Now, your SSIS package should be something like this:

Double click on the first Scrip Task. In the Script Task Editor:

• ScriptLanguage: Microsoft Visual C# 2012
• Set ReadOnlyVariables: User::Servers
• Set ReadWriteVariables: User::ServersList

• Click the “Edit Script…” button and add the following scripts in the main block:

            string array = Dts.Variables[“User::Servers”].Value.ToString();

            System.Collections.ArrayList list = new System.Collections.ArrayList();

            list.AddRange(array.Split(new char[] { ‘,’ }));

            Dts.Variables[“User::ServersList”].Value = list;

     Dts.TaskResult = (int)ScriptResults.Success;

• Build the code

• Close Visual Studio and then click OK on the “Script Task Editor”

Now, double click on the “Foreach Loop Container”:

• Go to “Collection” tab and select “Foreach From Variable Enumerator” as the Enumerator
• Select “User::ServerList” as the variable

• Go to “Variable Mappings”
• Select “User::ServerName” to map the collection value and click OK.

Double click on the second script task:

• ReadOnlyVariable: User::ServerName
• Click “Edit Script” and put the following script in the main block

MessageBox.Show(Dts.Variables[“User::ServerName”].Value.ToString());

Dts.TaskResult = (int)ScriptResults.Success;

• Build and close Visual Studio and finally click OK on the “Script Task Editor” window.

Now press F5 to execute the package.

All Done!

So you can replace the second Script Task with any other task that suits your case. For updating the CONFIG database sample case we discussed earlier in this article we’ll need to add another Foreach Loop Container and a Data Flow task to update the CONFIG database tables on different servers.

Enjoy!

Useful Links: http://forums.asp.net/t/1672662.aspx