Microsoft SQL Server: Foreach Loop Container in SSIS

Introducing the Foreach Loop Container

In this article, we look at another control flow component: the Foreach Loop container. You can use the container to loop through a set of enumerated items such files in a directory or rows in a table. The container points to whatever you wish to loop through and provides the mechanism necessary to carry out the looping.

On my system, I created the following four files in the C:\ C:\ForEach\Demo folder:

Create some Excel file

Devlopment. xlsx

Devlopment1. xlsx

Devlopment2. xlsx

Devlopment3. xlsx

Setting up the Foreach Loop Container

Our first step is to drag the Foreach Loop container from the Toolbox to the control flow design surface, as shown in Figure 1.

When adding the container to the control flow, you’ll see that it looks different from control flow tasks, such as ExecuteSQL. The Foreach Loop container will display the name of the container at the top with an empty box beneath it.

To configure the Foreach Loop container, double-click the container to launch the Foreach Loop Editor. The editor includes the following four pages:

General
Collection
Variable Mappings
Expressions

General Page

The General page includes the Name and Description properties, which you should define as you see fit. I’ve named my container Load file and given it the description Container to load file in database files.

That’s all there is to the General page. The important point to remember is to provide a meaningful name and description so that other developers can understand what’s going on.

Collection Page

Next, we move to the Collection page, which is where we select the enumerator type and configure any properties associated with that type.

The enumerator determines the type of objects that we plan to enumerate. The ForeachLoop container supports the following enumerator types:

Foreach File Enumerator: Enumerates files in a folder
Foreach Item Enumerator: Enumerates items in a collection, such as the executables specified in an Execute Process task.
Foreach ADO Enumerator: Enumerates rows in a table, such as the rows in an ADO recordset.
Foreach ADO.NET Schema Rowset Enumerator: Enumerates schema information about a data source.
Foreach From Variable Enumerator: Enumerates a list of objects in a variable, such as an array or ADO.NET DataTable.
ForeachNodeList Enumerator: Enumerates the result set of an XML Path Language (XPath) expression.
Foreach SMO Enumerator: Enumerates a list of SQL Server Management Objects (SMO) objects, such as a list of views in a database.

For this exercise, we want to use the Foreach File Enumerator type. However, when you first go to the Collection page, it’s not apparent how to configure the type. By default, the Foreach File Enumerator is listed, but no other configurable options are visible.

So the first thing we must do is to click in the Foreach File Enumerator listing. When the drop-down arrow appears to the right of the row, re-select Foreach File Enumerator. The options we must configure are then displayed on the Collection page.

For this exercise, we don’t need to define an expression, so we can leave the Expressions text box empty. The next step, then, is to configure the properties in the Enumeratorconfiguration section.

The first property we’ll configure is the Folder property. Click the Browse button to the right of the property. When the BrowseForFolder dialog box appears, navigate to the folder that contains the sample files you created for this exercise.

Next, we need to configure the Files property. This is where we specify which files to include in our list of enumerated files. For this exercise, we’ll load all excel files “xlsx” .” In the Files text box, enter “*.xlsx”.

Next, we need to select one of the following options in the Retrieve file name section:

Fully qualified: The fully qualified file name should be returned when a file in the enumerated list is being referenced.
Name and extension: The file name with its extension should be returned when a file in the enumerated list is being referenced.
Name only: The file name without its extension should be returned when a file in the enumerated list is being referenced.

For our example, we’ll select the fully qualified option.

Variable Mappings Page

Because we have not yet created a variable to map to our collection, we need to create one now. For this exercise, we’ll create the variable from the VariableMappings page. In the Variable column of the first row of the grid, click <New Variable…> to launch the Add Variable dialog box

Configure each property in the AddVariable dialog box as follows:

Container: Select the name of the current SSIS package. You want the variable to be created at the package scope.
Name: Type a name for your variable. I used FileName.
Namespace: Stick with the default: User.
Value Type: Select String if it’s not already selected.
Value: Leave this blank. It will be populated with the name of the current file each time the Foreach Loop container loops through your collection of files.
Read only: Do not select this checkbox. SSIS must be able to write to the variable.

Once you’ve completed configuring the variable settings, click OK.

Notice that the value User::FileName appears in the Variable column and the value 0 in the Index column. The Index value is specific to the ForeachItem enumerator and does not apply in this case. So we’ll stick with 0, the default value. Click OK to close the ForeachLoopEditor.

Add one data flow in contender.

To connect to our source files, we must set up a Excelconnection manager that points to those files. Right-click the ConnectionManagers window, and then click Connection manager for excel file. When the Excel Connection Manager Editor appears,. For the File option, enter the full path name to the first text file (Devlopment. xlsx) or click the Browse button, navigate to the file, and select it from the source folder.

Click OK to close the ExcelConnectionManagerEditor. The new connection manager should now be displayed in Connection Managers window. If you want, you can rename your connection manager. I renamed mine SourceFile.

Click the Expressions property so that the browse button (the ellipses) appears on the right-hand side, then click the browse button to open the Property Expressions Editor.

In the first row of the grid, select Connection String in the Property column. Then click the browse button at the far right of that row (to the right of the Expression column). This launches the Expression Builder dialog box. In the top-left window, expand the Variables node and drag the User::FileName variable to the Expression text box and make delay validation “True”