What is Control Flow?
Let me start by saying, “No Package on SSIS can be made without having a Control Flow”. Think of 'Control Flow' as a big container that contains a group of tasks to achieve the final goal. Well, 'Control Flow' is also known as the place for data preparation and final clean up.
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
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
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.
Note: In most of the images, you would see a red dot in the tasks. This is because the tasks are not yet configured and SSIS shows the red mark to highlight that this need attention.
Next, we will talk about Data Flow Task.