SSIS Containers

Hope the last article gave a good idea about some of the important control flow tasks.

Today we shall look into the major Control Flow Containers used in SSIS.

1.       For Loop Container-

The For Loop Container will iterate over tasks that we put inside the container for a predetermined number of times, or until a condition is met

(It is similar to the “For Loop” that we have in programming languages).

As in a typical Programming language expression “For (i=0; i<11; i++)”, we have 3 parameters that need to configured in For Loop Container-

a.       InitExpression      (equivalent to i=0)

b.       EvalExpression    (equivalent to i<11)

c.        AssignExpression (equivalent to i++)

E.g.- Let us assume a scenario where we need to download 10 files from a remote location directory every time the package executes;

       Implemented through a “For Loop Container” having a FTP task (as shown below).

           

2.       Foreach Loop Container-

The Foreach Loop container is used to iterate over a collection of objects (say a directory of files or a Recordset), performing tasks that are placed inside the container for every iteration. The loop implementation is similar to Foreach looping structure in programming languages.

E.g.- Rename all the files in a folder; Implemented through a “Foreach loop container” having a File system task in it (as shown below).

The Foreach loop container will iterate over all the files in that folder and the File System Task will rename each file the Foreach Loop iterates through.

3.       Sequence Container-

The Sequence container defines a control flow that is a subset of the package control flow. Sequence containers group the package into multiple separate control flows, each containing one or more tasks and containers. Sequence Containers are used to logically group a set of control flow tasks.

E.g.- Load data for different countries (stored in separate locations) into respective tables.

Note- All the above container will execute in parallel

*The SSIS Package is a container in itself (highest level container).