In some instances we have to do a single job for many SQL Server situations. Assume that now we have an online primarily based programme. The programme’s database is distributed throughout the nation and now we have 10 totally different digital (VM) servers to host the programme’s databases. The programme is working primarily based on some configurations which are saved in a CONFIG database. The CONFIG databases are hosted by 20 totally different SQL Server situations to serve 20 totally different shoppers. The SQL server situations are all named SQL server situations hosted by these 10 digital servers. We have to replace the CONFIG database for all areas on a month-to-month foundation. The database construction of all CONFIG databases is similar. On this case a easy manner is to create an SSIS bundle for every supply server to gather the info from all supply databases one-by-one. Because of this we could have 10 copies of the identical SSIS bundle that every bundle is pointing to a server as a supply server. We’d like 10 packages as a result of we are able to retrieve the CONFIG database record by writing a T-SQL script or utilizing an additional Foreach Loop Container. So we’d like a SSIS bundle per server.

The opposite manner is to create a dynamic resolution to gather the info from all supply databases hosted by totally different SQL server situations in a single SSIS bundle. On this case we have to have an inventory of supply SQL server situations as a variable. As a result of the truth that there isn’t a array record variable sort in SSIS, we have to make the answer work by changing a comma delimited string variable to an Object variable containing the record of servers. On this article we’ll characterize a dynamic option to work with totally different SQL Server situations. Our objective is to retrieve the record of SQL Server situations coming from a comma delimited string variable. So we’ll have a string like “SQLSRV01SQL2012,SQLSRV02SQL2008,SQLSRV02SQL2012,SQLSRV04” representing totally different SQL Server named situations

To realize the objective of making a dynamic resolution, observe the method under:

1. Create a brand new SSIS undertaking and title it “Dynamic Server Names”

2. Open the bundle

3. Create the next variables:

a. Servers; Knowledge sort: String. It’s an enter variable containing the SQL Server occasion names which are comma delimited.

b. ServersList; Knowledge sort: Object. It shops the record of servers transformed from the comma delimited string

c. ServerName; Knowledge sort: String. It accommodates every server title.

4. Add a script job to the management move. We have to make our arms soiled right here to transform the comma delimited string variable to an array record. The array record goes to be saved within the “ServersList” variable that’s an object variable.

5. Place a Foreach Loop Container to the Management Circulate

6. Place one other Script Process to the Foreach Loop Container. It is going to present the server title as a message to see if the answer works effective. Truly, you may put each different duties that you just want in your case.

7. Sort “SQLSRV01SQL2012,SQLSRV02SQL2008,SQLSRV02SQL2012,SQLSRV04” as an enter worth for the “Servers” variable

Now, your SSIS bundle ought to be one thing like this:

Double click on on the primary Scrip Process. Within the Script Process Editor:

• ScriptLanguage: Microsoft Visible C# 2012

• Set ReadOnlyVariables: Person::Servers

• Set ReadWriteVariables: Person::ServersList

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

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

            record.AddRange(array.Break up(new char[] { ‘,’ }));

            Dts.Variables[“User::ServersList”].Worth = record;

     Dts.TaskResult = (int)ScriptResults.Success;

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

Double click on on the second script job:

MessageBox.Present(Dts.Variables[“User::ServerName”].Worth.ToString());

Dts.TaskResult = (int)ScriptResults.Success;

Now press F5 to execute the bundle.

All Accomplished!

So you may substitute the second Script Process with some other job that fits your case. For updating the CONFIG database pattern case we mentioned earlier on this article we’ll want so as to add one other Foreach Loop Container and a Knowledge Circulate job to replace the CONFIG database tables on totally different servers.

Get pleasure from!

Helpful Hyperlinks: http://boards.asp.web/t/1672662.aspx