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.