What Is Parallel Execution In SSIS, How Many Tasks A SSIS Package Can Execute In Parallel?


In simple words, If you place more than one Task on Control Flow pane and do not connect them by using Precedence constraint, the Tasks will run in Parallel.

This can be helpful to speedup the process when we load data from Source Database to Staging Database and there is no dependency which table should be loaded first.

This is great , So If I need to load 100 staging tables from source database, I can run all of them in Parallel?

Yes, you can. Visit Link.

In this post, I am considering default settings, that means our SSIS Package will only be able to execute
 Total Tasks=Number of processors of machine+2.

How would I know that how many processes are on my machine?
Couple of ways to do that quickly

1-Connect to SQL Server by using SSMS if installed on the machine, Right Click on Instance Name and go to properties and then General and you will be able to see the number of processors.
Fig 1: Find Number of Processors from SQL Server Instance

2-Click on Start and then in Search write "Device Manager" and it will open Device Manager, Click on Processors and you will see them there.
Fig 2: Find out the Number of Processors on Computer by using Device Manager


My machine has 4 processors, So the max number of Tasks those can be executed by SSIS Package on my machine will be 4(processors)+2=6 with default setting.

As you can see the 6 Data Flow Tasks are executing parallel. Once any of them will complete, the next will start.
Fig 3: Parallel Execution in SSIS Package



6 comments:

  1. This is very useful. However I have 16 Physical Processors and I keep MaxConcurrentExecutables = -1 The default setting. So in my case,Total Tasks=Number of processors+2 = 16+ 2 =18. I have 10 DFTs and still its running only 6 at a time. Can you tell me why?

    ReplyDelete
    Replies
    1. it counts the number of logical processors.

      Delete
    2. I also believe that Engine Threads in the data flow task(s) count towards it. I am currently looking at this myself.

      Delete
  2. really ,,,,,,, a smooth way to guide.

    ReplyDelete