SSIS Control Flow Containers

Introduction

To continuing my journey on SSIS, this article contains related to Control Flow Containers. It is important to understand the containers of control flow. As per me very SSIS developer must have a solid concept related to the Containers of Control flow.

Type of Control Flow

There are three primary types of control flow objects.

1.    Control flow Tasks: Workflow objects that perform operational-level jobs.

2.    Control flow Containers: It’s grouping mechanisms for tasks and other containers.

3.    Constraints: Allow to connects tasks and define execution ordering and precedence.       

Control flow containers

When we open the SSIS BIDS for control flow tables and the Control Flow Items in the tools box we can see the three containers placed at the top of the others.

Microsoft places it as the top positions they are frequently used by the SSIS Developers.

Why it’s needed

A SSIS package must contains a single control flow tasks. However most of the time a package contains several control flow tasks and they coordinate each other. So in this condition we must organize the tasks and for that we use the control flow containers. So a control flow contains used to group the tasks together. The control flow containers group the tasks together for parallelization, ordering, logging and transactions.

We can think the control flow containers as a folder and contains the other sub folder and file. Here the control flow containers contain different tasks like file in the folder and others containers like subfolder within folder.

How to add containers

To add a control flow containers just drag it from control flow item tool bars to control flow work space of BIDS.

Type of Control Flow Containers

There are three primary types of control flow containers

1    .    Sequence Containers

2    .    For Loop Containers

3    .    ForEach Loop Containers

      Sequence Containers

      Most commonly used containers by SSIS developer. It’s used to organize subordinate tasks by grouping them together and let us applies transactions or assigns logging to the containers.

       For Loop Container

     Provide the same functionality as sequence container except that they can let us run the tasks within them multiple times, based on any condition. Think it as a loop. For example for loop that runs from 1 to 100 times.

      ForEach Loop Conatiners

      Allow us looping but instead of providing a condition expression we loop over a set of objects such as files in a folder.

     If we takes a simple example think we have 10 clients and they upload there data to an FTP server by 10 different txt file. The SSIS program must check the format of each txt file before read and store the data in Table objects of MS SQL Server.

Common Properties of Containers

There are some common properties of all the containers are mentioned bellow. We can find the details in MSDN http://msdn.microsoft.com/en-us/library/ms137728(v=sql.100).aspx

Let’s take a simple example to understand the Control Flow containers.

Example of Sequence Containers

1    .    Drag the Sequence Containers from Control Flow Items tools bar to Control Flow work 

         space. Drag 3 Execute SQL Task to the Sequence Containers.

2    .    Edit the Execute SQL Task by right click each tasks and select Edit from popup menu.

3    .    In SQL StatementàConnection Select the OLEDB Connection and in SQL Statement  

         property just Write SELCT 1.

4   .    In the 2^nd Execute SQL Tasks for the property SQL statement write something which in not a 

        sql statement to create Error.

5   .    Now go to the property of the Sequence containers and set the transaction option property to 

        Required.

6   .    Now run the package.

Related Tropics

1    .    Understand the BIDS

         http://www.sqlknowledgebank.blogspot.in/2013/01/understanding-bids.html

2   .    SSIS Connection Manager
        http://www.sqlknowledgebank.blogspot.in/2013/02/ssis-connection-manager.html

3   .    Introduction to Control Flow and Data Flow
        http://www.sqlknowledgebank.blogspot.in/2013/02/introduction-to-control-flow-and-data.html

References
http://msdn.microsoft.com/en-us/library/ms137728(v=sql.100).aspx

Hope you like it.

Posted by: MR. JOYDEEP DAS