SSIS How to Process Data as Fastest,Parallel , Multithreaded or in Very Efficient Way !!!

I have recently been curious to implement parallelism of different flows in Integration Services Packages in my current project.

Where project requirement is to process Data as fast as we can with all parallel, Multithreading or by any other way in very less span of time.

After digging in to a lot of things I realize there are n number of ways and n number of post available

  1. I can use Multicasting of My Task Components
  2. I can use Multithreading in my similar Tasks
  3. I can use Balance Data Load Distributer where ever required
    Data flow pipeline engine can divide a Path (which was called Execution Tree in SSIS 2005) into several Sub Paths – each one can be executed on its own processor/thread. This threading optimization promises to have many more enhancements on performance in other parts of SSIS such as packages with deep parallel execution paths. With increasing performance on most systems, data flow pipeline engine reduces the need for manual configuration of SSIS packages to increase parallelism, and therefore increased developer productivity.The pipeline allocates four threads to work on these four sub paths in parallel and utilizes the hardware resources and improves the performance.

How to use SSIS MaxConcurrentExecutables property ?

Let say I have a master package with 10 execute package tasks that I need to execute in parallel. But only 6 of the 10 packages execute at one time. I won’t go into why all 10 need to execute in parallel.

The server has 4 processors and I had the MaxConcurrentExecutables property of the master package set to -1. (This equates to 4 + 2 concurrent exe’s). I also have each execute package tasks ExecuteOutOfProcess property set to true.

So, I tried to up the number from -1 to 10. Well this worked great when running the master package within BIDS.

Nice example is given on following following link with lots of explanation

Some More Tips and Link

I have already written on complete post for the explanation of balance data distributor click here to see that post :

Whole Lot About “Balanced Data Distributor”-New SSIS Component « (B)usiness (I)ntelligence Mentalist

At Last what I have done after all reading and googling lot about implement following strategies

  • Use multicasting where Different independent task for parallel execution
  • Use For Each loop container with MaxConcurrentExecutables where is common Set of module to run parallel module may contain multiple task
  • Balance Data Distributer to speed up data transformations, so it won’t have an effect on single-processor configurations

Hope this Helps !!

Quote of Day:

A joyful heart is the inevitable result of a heart burning with love. ~ Mother Teresa

9 thoughts on “SSIS How to Process Data as Fastest,Parallel , Multithreaded or in Very Efficient Way !!!

  1. MaxConcurrentExecutables depends on how much SQL Server can hold parallel, This you can do by adding and removing.
    But Parallel Processing with continuous serial processing is key for performance optimization.

  2. Hello Sir,
    its really one of best post on parallelism. here you provide nice links for deep dive into SSIS parallel execution,
    but stuck with a question :
    how can we calculate the maximum value of MaxConcurrentExecutables?
    for example I have 10 or more than 10 package task which i want to run parallely, so can i set the value of MaxConcurrentExecutables = number of package Task which i want to run parallely.
    or we have any dependency/Limitation to set this value.

    Vijay Labhaniya

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s