Working on completely different supply SQL Server situations in a single SSIS package deal
In some circumstances we have to do a single job for plenty of SQL Server situations. Assume that we now have an online based mostly programme. The programme’s database is distributed throughout the nation and we now have 10 completely different digital (VM) servers to host the programme’s databases. The programme is working based mostly on some configurations which might be saved in a CONFIG database. The CONFIG databases are hosted by 20 completely different SQL Server situations to serve 20 completely different purchasers. 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 approach is to create an SSIS package deal for every supply server to gather the info from all supply databases one-by-one. Which means we may have 10 copies of the identical SSIS package deal that every package deal is pointing to a server as a supply server. We’d like 10 packages as a result of we will retrieve the CONFIG database checklist by writing a T-SQL script or utilizing an additional Foreach Loop Container. So we’d like a SSIS package deal per server.
The opposite approach is to create a dynamic answer to gather the info from all supply databases hosted by completely different SQL server situations in a single SSIS package deal. On this case we have to have an inventory of supply SQL server situations as a variable. As a consequence of the truth that there is no such thing as a array checklist variable kind in SSIS, we have to make the answer work by changing a comma delimited string variable to an Object variable containing the checklist of servers. On this article we’ll characterize a dynamic method to work with completely different SQL Server situations. Our purpose is to retrieve the checklist of SQL Server situations coming from a comma delimited string variable. So we’ll have a string like “SQLSRV01SQL2012,SQLSRV02SQL2008,SQLSRV02SQL2012,SQLSRV04” representing completely different SQL Server named situations
To realize the purpose of making a dynamic answer, observe the method beneath:
1. Create a brand new SSIS venture and title it “Dynamic Server Names”
2. Open the package deal
3. Create the next variables:
a. Servers; Information kind: String. It’s an enter variable containing the SQL Server occasion names which might be comma delimited.
b. ServersList; Information kind: Object. It shops the checklist of servers transformed from the comma delimited string
c. ServerName; Information kind: String. It incorporates every server title.
4. Add a script job to the management movement. We have to make our palms soiled right here to transform the comma delimited string variable to an array checklist. The array checklist goes to be saved within the “ServersList” variable that’s an object variable.
5. Place a Foreach Loop Container to the Management Movement
6. Place one other Script Process to the Foreach Loop Container. It’s going to present the server title as a message to see if the answer works advantageous. Really, you possibly can put each different duties that you just want in your case.
7. Kind “SQLSRV01SQL2012,SQLSRV02SQL2008,SQLSRV02SQL2012,SQLSRV04” as an enter worth for the “Servers” variable
Now, your SSIS package deal needs 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: Consumer::Servers
-
Set ReadWriteVariables: Consumer::ServersList
string array = Dts.Variables[“User::Servers”].Worth.ToString();
System.Collections.ArrayList checklist = new System.Collections.ArrayList();
checklist.AddRange(array.Cut up(new char[] { ‘,’ }));
Dts.Variables[“User::ServersList”].Worth = checklist;
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 package deal.
All Finished!
So you possibly can change the second Script Process with every 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 Information Movement job to replace the CONFIG database tables on completely different servers.
Get pleasure from!
Helpful Hyperlinks: http://boards.asp.web/t/1672662.aspx
