SSIS - Control Flow Basics

Overview

Components which use in Control flow to perform sequence of operations.

• Connection Manager
• Tasks
• Precedence Constrains
• Containers

Connection Manager

Microsoft SQL Server Integration Services packages use connections to perform different tasks and to implement Integration Services features.

Type	Description	Topic
ADO	Connects to ActiveX Data Objects (ADO) objects.	ADO Connection Manager
ADO.NET	Connects to a data source by using a .NET provider.	ADO.NET Connection Manager
CACHE	Reads data from the data flow or from a cache file (.caw), and can save data to the cache file.	Cache Connection Manager
DQS	Connects to a Data Quality Services server and a Data Quality Services database on the server.	DQS Cleansing Connection Manager
EXCEL	Connects to an Excel workbook file.	Excel Connection Manager
FILE	Connects to a file or a folder.	File Connection Manager
FLATFILE	Connect to data in a single flat file.	Flat File Connection Manager
FTP	Connect to an FTP server.	FTP Connection Manager
HTTP	Connects to a Web server.	HTTP Connection Manager
MSMQ	Connects to a message queue.	MSMQ Connection Manager
MSOLAP100	Connects to an instance of SQL Server Analysis Services or an Analysis Services project.	Analysis Services Connection Manager
MULTIFILE	Connects to multiple files and folders.	Multiple Files Connection Manager
MULTIFLATFILE	Connects to multiple data files and folders.	Multiple Flat Files Connection Manager
OLEDB	Connects to a data source by using an OLE DB provider.	OLE DB Connection Manager
ODBC	Connects to a data source by using ODBC.	ODBC Connection Manager
SMOServer	Connects to a SQL Server Management Objects (SMO) server.	SMO Connection Manager
SMTP	Connects to an SMTP mail server.	SMTP Connection Manager
SQLMOBILE	Connects to a SQL Server Compact database.	SQL Server Compact Edition Connection Manager
WMI	Connects to a server and specifies the scope of Windows Management Instrumentation (WMI) management on the server.	WMI Connection Manager

Tasks

Tasks can be added to "Control flow Tab". Each task will perform one unit of work.

Let us add a task "Execute  SQL Task. Which can execute SQL Query.

In the task we can observe there is red color cross mark. Which indicates that there is a problem in the task and can not be executed. Edit the task by double clicking and add the connection string and SQL Script to execute. 

Each task is having it's own behavior. To learn more about tasks refer the URL.
http://msdn.microsoft.com/en-us/library/ms139892(v=sql.105) 

Precedence Constraints.

•     Sequential Execution

    To force the task precedence use Precedence Constraints. 

Sequential execution

     In the Sequence execution task execution occurs in the sequence.

•     Parallel Execution

    In the parallel execution if there no precedence constraint is defined.
    All task can execute simultaneously with out depending on other task execution status.

Parallel execution

                                                           
 In the sequential execution we have different constraints.
 Right click on constrain arrow, there we can see

• Success
• Failure
• Completion constrains.

Click on the Constraint Line and select Edit to see all constraints.

    Different Constraint combinations

For more details: http://msdn.microsoft.com/en-us/library/ms141261
Task color indications
After execution of task, task may b filled with three different colors.
Yellow: Task started and in progress
Red:     Task Failed
Green : Task Successful.
Once task started it may go to the Success or Failure state.
If the task is failed we need to check the task properties.

Containers

We have three different containers.

• Sequence Container
• For loop Container
• Foreach Loop Container

Sequence Container

In sequence container tasks execute together.

In the Sequence container we can execute Parallel and Sequential tasks.
All the tasks in the sequence container can be executed only once.

Sequence containers can be used to

• Set properties once for multiple task.
• Control subsequent flow.

Set properties once for multiple task:

Some of the properties for all tasks are common. Those can be set at container level.

Control subsequent flow:

If any one of the task inside the container is failed, then we can stop the execution of subsequent step and we can take further action on failure of entire control.

For Loop Container

For loop container can be executed number of times depending on the iteration defined in the loop.

For loop container is having three expression in the properties where we can define the for loop expression.

In the "InitExpression" we can initiate the for loop counter.
In the "EvalExpression" we can set the number of enumerations. Loop exits after reaching the defined value in this.
In the "AssignExpression" we can set the increment.

ForEach Loop Container

Foreach Loop Container in SSIS

It defines a repeating control flow in a package.  The loop implementation is similar to Foreach looping structure in   programming languages but in a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator. 

Foreach Loop Container using Foreach File enumerator. I'll move data of multiple excel sheets into a database table.

Let us take a sample spread sheet.

Create a SSIS Package.

Drag and drop Foreach Loop Container from Toolbox. Add a package  variable FileName of String type and assign default value C:\SSIS\Test\Sales.xlsx. I'll use this variable to store File Name of each iteration.

Drag and drop Data Flow Task inside Foreach Loop Container and rename it with DFT - Load Data From Excel.

Double click the Foreach Loop container to open Foreach Loop Editor to configure the Foreach Loop container. Click on Collection and provide the folder that contains the files to enumerate (in our example C:\SSIS\Test), specify a filter for the file name and type*.xlsx and specify the fully qualified file name as retrieve file name as shown below:

Click on Variable Mappings and Select variable User::FileName from dropdown box. Corresponding Index for this variable will be 0. Click OK to proceed and save changes.

Add Excel Connection Manager to read data from Excel sheets:- Right click on Connection Managers area and select New Connection... and select EXCEL as type and click on Add button. Enter C:\SSIS\Test\Sales.xlsx in Excel File Path and make sure that checkbox First row has column names is checked. Click OK to proceed and save changes.

Config ConnectionString with FileName variable:-

Click on Excel Connection Manager--> go to properties --> Click on Expression -->

This will openProperty Expression Editor. SelectExcelFilePathfrom Property dropdown box. Click on Expression and Drag and drop User::FileName from Variables. Click OK two times and save changes.

Add OLEDB connection manager to load data into database table: Right click on Connection Managers area and select New OLEDB Connection... This will open Configure OLE DB Connection Manager. Click onNew button. EnterServer name anddatabase name in respective boxes as shown below:

---Image nee to be added---

Double click on Data Flow Task to go to Data Flow tab. Drag and dropExcel Source fromData Flow Sourcesand double click on it to open Excel Source Editor. SelectExcel Connection Manager inOLEDB connection manager andSheet1$ in Name of the Excel sheetdropdown boxes.

Click on Columns in left side window. This will display all the External Columns available in excel and Output Columns. Click OK to process and save changes.

Drag and drop OLEDB Destination from Data Flow Destinations. Click on Excel Source to see green and red output lines. Connect green output line to OLEDB Destination. Now double click on OLEDB Destination to open OLE DB Destination Editor. Selectlocal.Test as OLE DB connection manager. Click on New... button to create Destination table. It will open Create Table wizard. You can edit T-SQL statement as per your need. You can change the name of table if you wish. Click OK to proceed.

---Image nee to be added---

Click on Mappings to map source and destination columns. It will automatically map the columns by their matching names. However you can map the columns manually as well. Click OK to process and save changes.

---Image nee to be added---

We are done with package development. Finally package looks one as shown below:

---Image nee to be added---

Now its time to execute the package and check the result. 

Go to package location D:\SSIS\Hari\Sample\SSIS-Sample1 --> Right Click on packageForeachLoopContainer.dtsx and click SQL Server 2008 Integration Services Package Execution Utility. This will open Package Execute Utility wizard.

---Image nee to be added---

Click on Executebutton. A new wizard Package Execution Progress will open. You can see progress of package execution in this wizard.

---Image nee to be added---

Now you can check the data of all excel files into database table as shown below:

---Image nee to be added---