SQL Server Integration Services – Control Flow Objects

So what is Control Flow?

Control Flow is the enginewhich manages the workflowof the tasks created coupled with the control flow containers and constraints. SSIS provides a Design UI which displays a workspace where we can configure different control flow objects.

SSIS provides threetypes of control flow objects
1) Tasks– Tasks are objects that perform specific work
2) Containers– Containers help group different tasks
3) Constraints– Constraints help connecting different tasks and containers. Its also helps to define the order of execution of different tasks.

A package must contain at least one task that performs a certain operation. When multiple tasks are configured, then a container can be used to group them together.

Container

SSIS provides three types of Containers. Here is a brief description about the three Containers. We will deep dive into the Containers later.

1) Sequence Container : This allows us to group multiple tasks together. The benefits of this feature is that we can control the entire group rather that managing each task separately. It also means we can run different tasks sequentially or parallel. The entire group can collapsed or expanded. The Sequence container is the 3rd container in the toolbox2) For Loop Container : This provides the same functionality as the sequence container but also lets you run tasks multiple times based on the condition provided. The For Loop is the 1st tool in the tool box3) Foreach Loop Container : This allows looping but instead of a condition the looping happens over objects such as files and folders or tables in a database.

Implementation of Control Flow Task

We will now implement the above by creating and editing a control flow task and executing the package within the SSIS environment. We will be using the same project we created in our previous article for this demo.

Step 1: Open the Project using BIDS -> Select Mypackage.dtsx -> Click on the Toolbox -> Drag and Drop the Execute SQL Task on the Designer Surface. Rename the task as Update Table
Step 2: Right Click on the Task– > Click on Edit -> Change the Connectionto AdventureWorks-> Click on the SQLStatementand insert the below query – > Click on OK.Query:

Step 3: click Start Debugging on the Standard toolbar -> If the execution is correct then the task would be green.Step 4: Click on the Progress tab to view the execution details of the task. -> Stop the execution by clciking on the stop debugging button – > Save the project.

The objective of this article was an introduction to the concept of Control Flow and Taskswith a small demo. I hope I was able to do that. In my next article I will write about Container with a broader perspective.