Parallel Processing using C# in a SSIS 2012 Execute Script Task for calling multiple DTSX packages stored at DTS or File System
-
Hi Gents,
I have landed into a scenario where we have around 60 to 70 SSIS packages that would be loading data into the datawarehouse.
Most of the individual SSIS packages would be loading small to medium sized volume ranging from few thousands to around 10 million records.
As of now, I have created a master SSIS package, added a script task and wrote a C# script to invoke the packages dynamically by using the package locations stored in a database table.
However, the packages execute sequentially that is very time consuming and not utilizing the server processing capability to a decent mark.
Kindly help me in suggesting a wayout/solution where I can execute multiple SSIS packages in parallel.
As of now, I am hoping to have around 6 to 8 parallel execution threads, if possible.
I am looking for a solution that is completely dynamic.
Kindly suggest if what I am thinking is possible. If yes, How? If now, Why?
Please reply if you require further details in order to help.
-
anupam.jha (12/20/2016)
Hi Gents,I have landed into a scenario where we have around 60 to 70 SSIS packages that would be loading data into the datawarehouse.
Most of the individual SSIS packages would be loading small to medium sized volume ranging from few thousands to around 10 million records.
As of now, I have created a master SSIS package, added a script task and wrote a C# script to invoke the packages dynamically by using the package locations stored in a database table.
However, the packages execute sequentially that is very time consuming and not utilizing the server processing capability to a decent mark.
Kindly help me in suggesting a wayout/solution where I can execute multiple SSIS packages in parallel.
As of now, I am hoping to have around 6 to 8 parallel execution threads, if possible.
I am looking for a solution that is completely dynamic.
Kindly suggest if what I am thinking is possible. If yes, How? If now, Why?
Please reply if you require further details in order to help.
SSIS does parallel just fine, but if you've connected your items with precedence constraints, you may have made your design a serialized stack of data flows.
this picture for example, shows SSIS will execute nine things in parallel (which would probably be four or eight threads
what does your package look like?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
It's completely possible, but you'll need to write some sort of framework (or buy one) to do it. It's tough to describe in just a few sentences, as it required a couple of weeks to develop.
It's based around a single master package which accepts a Job Name parameter when it executes.
When the package executes, it reads meta data for the Job Name and determines which packages have to be executed, in which order, and for each one whether parallelism is allowed and writes all of this info to a package execution queue.
The master package then orchestrates the execution of all the packages in the queue, in the specified order, with parallelism where allowed.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Hi Lowell,
My package looks like:
1.
BatchMaster.DTSX - 1 Script Task programmed in C# to programmatically load DTSX packages from File System.
2.
DTSX Packages (placed within a directory/FileSystem) to be loaded and executed from BatchMaster.DTSX would be carrying complex logic to load specific tables from staging server. The count of these packages would be around 60 or 70.
3. The location of these packages is stored in a database table from where the location is read and the particular package is loaded.
Later, these packages would be deployed into SSISDB as an enhancement.
Dummy Code in C#:
var getPkgsToLoadFromDB = "SELECT ID, PkgName, PkgLocation+PkgName+'.DTSX'"
+ "PkgUNCPath FROM dbo.PackageMaster";
DataTable dt = ReadFromDB(getPkgsToLoadFromDB);
int i = 0;
foreach (DataRow dr in dt.Rows)
{
Microsoft.SqlServer.Dts.Runtime.Application app;
app = new Microsoft.SqlServer.Dts.Runtime.Application();
Package[] pkg = new Package[dt.Rows.Count];
DTSExecResult pkgResults;
String loc = (string)dr["PkgUNCPath"];
pkg = app.LoadPackage(loc, null);
Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = pkg.Execute(null, myVars, null, null, null);
i = i + 1;
}
-
change that foreach to a parallel.foreach is probably an option.
https://msdn.microsoft.com/en-us/library/dd460720(v=vs.110).aspx has a simple example.
and if required look for other examples on the net.
-
frederico_fonseca (12/21/2016)
change that foreach to a parallel.foreach is probably an option.https://msdn.microsoft.com/en-us/library/dd460720(v=vs.110).aspx has a simple example.
and if required look for other examples on the net.
And here's that link again, in clickable format:
https://msdn.microsoft.com/en-us/library/dd460720(v=vs.110).aspx
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply