Share via

Parallel Processing in SSIS

Khidir Elsanosi 21 Reputation points
2020-08-26T13:24:30.52+00:00

Dears

I have the following scenario in SSIS. There are two packages, Outer.dtsx and Inner.dtsx. the Inner package is called inside the Outer package in the workflow. To increase the performance, as the workload is heavy, I added a sequence container, and instead of having only one Inner package running, I managed several packages inside the container, so to have multiple instances (10 to be exact) of Inner package running in parallel. It is only one version of Inner package, however it is called several times.

How does this scenario differ from having multiple versions of Inner (Inner_1, Inner_2, ...... , Inner_10) and run them again inside the sequence container? Does having multiple packages with same definition improves the performance, compared to one version of the package, called several times ? Which scenario is more efficient and has best performance ?

I hope I explained myself clear.

Thanks and Regards

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,467 questions
0 comments
Accepted answer
  1. Yitzhak Khabinsky 25,201 Reputation points
    2020-08-26T13:59:14.573+00:00

    Generally speaking, there is no need to create multiple physical copies of the same package.
    It is possible to call the same single package in parallel.
    So your first method will work.

    Additionally, you can use SSIS For Loop Container, its counter <=10.
    Inside of it Execute Package Task, with ExecuteOutOfProcess setting as True or False.

    ExecuteOutOfProcess: Specifies whether you want your child package to run in the same process (with the parent package) or the separate process.

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2020-08-26T14:14:04.163+00:00

    I do what you describe all the time. There is no difference calling the same SSIS package (with different parameters) vs different SSIS packages.

    SSIS only runs X processes in parallel. So there are a lot of variables on performance depending on your hardware, CPU, RAM ,etc.

    You have to test in your environment and figure out where the "sweet spot" is. With my hardware, I was able to run 13 parallel packages before I overloaded the SQL Server.