Parallel Loop Task


SQL Server 2005, 2008, 2008R2, 2012, 2014

SQL Server 2005, 2008, 2008R2, 2012, 2014

Looking for older releases? Check here.

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

Name

Specify the task name.

Description

Specify the task description.

StopOnFailure

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

PackageConnection

Specify the package connection.

ForEachLoop

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.

MaxExecutables

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 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