SSIS - Control Flow

In Control Flow, we set up the flow of jobs in an ETL process, like fetching the files from ftp location to local and then make the file ready for ETL or at times, we clear the staging tables before the ETL process. All such tasks are done before ETL commences.

Once the ETL completes, we use Control Flow for tasks like sending mail updates, achieving files, etc.

Control Flow primarily contains various tasks and precedence constraints, which connects these tasks in a logical sequence. This creates the flow of control among the tasks.

Few of the various tasks available in Control Flow are:

• For Loop For Each loop Script Task
• Send Mail Task Execute SQL Task Data
• Flow Task FTP Task ActiveX Script
• Task Execute DTS 2000 Package Task
• Execute Package Task Execute Process
• Task XML Task
• Transfer Database Task

........ etc.

In most of the cases, the names are self-explanatory. For others we will discuss in details.

A meaningful package would contain at least one task. In most of the cases, we have a number of the above tasks in a package with a particular task used more than once based on the requirement. Now we can have the tasks linked to each other or each task independent of the other or a group of tasks that need to follow a sequence and other group of tasks need to follow a sequence. However, these 2 groups are mutually exclusive and are not dependent on each other. In such cases, we need to join the 2 groups of tasks.

Single Flow in a package

Multiple Groups, which run in parallel

Precedence Constraint

Now how do we group create a flow between two tasks or link two Tasks?

When you drag a particular task from the Tasks pane onto the designer, you will see a GREEN arrow coming from the task. All you need to do is drag this arrow and connect to another task on the designer. Therefore, the Flow would be from the task, which had the arrow initially to the Task to which it is connected. This Green arrow is called the PRECEDENCE CONSTRAINT and this comes very handy while designing packages.

One Task could link with multiple tasks.

Similarly, multiple tasks could lead to one task.

Precedence constraint need not always be green. It can have colours as Green, Blue or Red. It need not always be a continuous line. It could also be a dotted line. We will talk about all this later in detail.