SQL Server Integration Services (SSIS) is a Business Intelligence tool which can be used by database developers or administrators to perform extract transform & load (ETL) operations. SQL Server Integration Services has come long way since the early days of Data Transformation Services (DTS) which was initially introduced by Microsoft in SQL Server 7.0. Most database developers and administrators had their first interaction with Data Transformation Services (DTS) when they used the popular Import and Export Wizard. The primary objective of DTS was to transform data from the OLEDB data source to another data type. In SQL Server 2008 Integration Services, there are approximately 28 different control flow tasks and 11 Maintenance Plan Tasks.
Container Tasks in SSIS 2005 & Later Versions
The container control flow tasks within SSIS can be used by database developers or database administrators when they want to provide a structure to the package and to repeat control flows within the package. It can also be used to group tasks and containers within a package into a meaningful units of work. The types of container tasks are : For Loop Container, Foreach Loop Container, Task Host and Sequence Container. However the Task Host containers task is not visible within the control flow toolbox, this is designed to provide service to a single task. The container tasks within SSIS are shown in the snippet below.
While designing SSIS packages if you want to group two or more tasks together then you can select those tasks, right click them and choose the Group option from the drop down. This will group the tasks together as shown in the snippet below.
Example – For Loop Container
The For Loop Container task in SSIS 2005 and later versions can be used to introduce a looping mechanism within your SSIS package. For example:
1. Create a new SQL Server Integration Services Project and rename the default package ForLoopExample.dtsx
2. 2.Double click the ForLoopExample.dtsx package to open it up in Design mode.
3. 3.Add a variable named “Iteration”; you can open up the Variables window by right clicking within the control flow design window and by choosing the Variables option from the drop down list as shown in the below snippet.
4. Once the variables window opens up, click on Add Variable button and provide the details as shown in the below snippet.
5. Drag and drop “For Loop Container” from the toolbox to the Control Flow window. Double click the for loop container to open up the editor window. Here you will be able to see the below mentioned three For Loop properties
InitExpression: – Specifies an expression that initializes variable values which can be used within the loop.
EvalExpression: – Specifies an expression that stops the loop when the expression evaluates to be False.
AssignExpression: – Specifies an expression that changes a condition in the same way each time the loop iterates.
Configure the For Loop Container as shown in the below snippet and click OK to save the changes.
6. Next step will be to drag a Script Task from the toolbox and drop it within the “For Loop Container”. Double click the Script Task to open up the editor. In SSIS 2008 you can also use C# as the scripting language other than the traditional Visual Basic. Set the ReadOnlyVariables property to Iteration as shown in the below snippet.
Finally click on Edit Script… to open up the visual studio designer.
7. You will need to replace the Main() subroutine with the code shown in the below snippet. This code reads the variable and pops up the message box with the @Iteration variable. Once the necessary changes are done, save and exit the Visual Studio Designer Environment.
Public Sub Main()
‘
‘ Add your code here
‘
Try
MsgBox(Dts.Variables(“Iteration”).Value)
Dts.TaskResult = ScriptResults.Success
Catch EX As Exception
Dts.TaskResult = ScriptResults.Failure
End Try
End Sub
8. Finally to execute the package, right click ForLoopExample.dtsx package from the solution explorer and select Execute Package option from the drop down list. Once the package has successfully executed you will be shown the below screen:
