SSIS - Control Flow

In SQL Server Integration Services, a package is a graphical design tool which may consist of organized collection of connections, control flow elements, data flow elements, event handlers, variables, parameters, and configurations.

A control flow is the starting point of any package which defines a workflow of tasks to be executed, often a particular order. It is collection of three different types of control flow elements such as containers, tasks and precedence constraints.

Without these control flow elements, a control flow task is not cable to do nothing in itself to the data. It defines the data movement and transformation from source to destination.

If we try to define these control flow elements then we can say that –

Containers that provide structures in packages,

Tasks that provide functionality in the package and

Precedence constraints that connect the executable, containers, and tasks into an ordered control flow.

As we know that SSIS architecture supports the nesting of containers, and a control flow can include multiple levels of nested containers.

The following diagram shows a control flow that has two container and seven tasks. Five of the tasks are defined at the package level, and two tasks are defined at the containers level which are inside those containers. Each task and containers are linked with each other with the help of precedence constraints.

According to data process requirements,  a package could contain a container such as a Foreach Loop container, which in turn could contain another Foreach Loop container and so on. Most interesting thing is that Event handlers also have control flows and they are built using the same kinds of control flow elements.

Control Flow Implementation

A control flow control is responsible to provide the logic for when data flow components are run and how they are run. Also control flows can: perform looping, call stored procs, move files, manage error handling, check a condition and call different tasks depending on the result, process a cube, trigger another procedures, etc.

When the Control Flow tab is active, the Toolbox lists the tasks and containers that you can add to the control flow.

The following diagram shows the control flow of a simple package in the control flow designer. The tasks and container are connected by using precedence constraints.

Many tasks require a connection to a data source, and you have to add the connection managers that the Related Tasks task requires to the package. Depending on the enumerator type it uses, the Foreach Loop container may also require a connection manager.

SSIS Control Flow performs operations; such as executing a SQL Statement or Sending an email and provide a data flow from one task to another task or containers with the help of precedence constraints.