Saturday, 16 February 2013

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 2nd 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


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






Hope you like it.




Posted by: MR. JOYDEEP DAS

1 comment:

  1. Task Host Containers
    --------------------
    One thing that I let in this article is "Task Host Containers".

    The Package itself is a container and would be considered the root foundation in this hierarchy and it is called the Task Host Containers.
    In the control flow when we add the containers like Sequence Container or a For Loop Container etc, we actually add the containers under Task Host Containers. That means the containers within container. If we add the single tasks like Script Task it will be joined into a Task Host Container. The Task Host Container is not in the Toolbox, but each Task will be placed in its own Task Host Container by default.

    ReplyDelete