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
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
6 . Now run the package.
1 . Understand the BIDS
2 . SSIS Connection Manager
3 . Introduction to Control Flow and Data Flow
Hope you like it.
Posted by: MR. JOYDEEP DAS