Overview

The Parallel Loop Task is an SSIS Control Flow task, which can execute multiple iterations of the standard Foreach Loop Container concurrently. Based on actual tests, when a CPU-intensive process is run on a 4-core machine using this Parallel Loop Task, it executes 3 times faster as compared to running the same process sequentially.


Setup

Use the General page of the Parallel Loop Task Editor dialog to configure the parameters needed to execute a loop in parallel.

Parameters

Specify the task name.

Specify the task description.

Specify whether or not the Parallel Loop Task terminates if parallel execution fails.

Specify the package connection.

Type the Foreach Loop you want executed concurrently, or click the browse button () and locate it. You must disable the target loop because the loop execution will be done by the Parallel Loop Task.

Specify the number of threads to use for concurrent execution. A value of -1 allows the maximum number of concurrently running iterations to equal the number of processors plus two. The specified value is constrained to be equal to or less than the Packages' MaxConcurrentExecutables property.


Considerations about Foreach Loop iteration variable

It is mandatory to use an iteration variable scoped inside the loop container. Failure to do this will result in errors and/or concurrency issues.


How to set up a dynamic loop connection

Follow the steps below to set up a dynamic iteration-variable dependent connection:

  1. Create a variable with the package scope and use this variable in the connection manager to construct your expression.
  2. Create a new variable with the same name and type, but with the loop scope.


The Parallel Loop Task will use the variable with the loop scope to evaluate the connection manager expression and build the dynamic connection.


Samples

  • For a sample (SQL Server 2005 format) demonstrating concurrent compression with the COZYROC Zip Task, check this package. The package contains loops, demonstrating both variable and connection iterations.
  • Introducing ETL Assistant – A Dynamic, Templated Approach to SSIS by Eric Whitley (SQL Server 2008 format)

What's New

1.7 SR-1
  • Fixed: Failed when project connections were used.
1.6 SR-4
  • Fixed: Failed under SQL 2012 and above with error "Changing the Value of a variable failed because it is a parameter variable. Parameter variables are read-only." when package contained parameters (Thank you, Ankit).
1.6 SR-3
  • Fixed: Failed under SQL 2012 with error "A deadlock was detected while trying to lock variables "..." for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out." when Project parameters were defined (Thank you, Ecki).
1.5 SR-4
  • Fixed: Task now provides better logging information (Thank you, Jonathan).
  • New: Task can now process parallel loops defined inside For Each Loop (Thank you, Clement).
1.5 SR-3
  • Fixed: Executing child package containing Parallel Loop Task with inherited variable from parent package failed with error: "The object cannot be added because another object with the same name already exists in the collection. Use a different name to resolve this error." (Thank you, Jacob).
1.5 SR-2
  • Fixed: Task didn't update package-scoped variables used in the loop.