SSIS ForEach Loop File Enumerator

The SSIS ForEach Loop container will repeat the control flow task for N number of times, similar to the Foreach loop in any programming language. The SQL Server Integration Services provides eight types of enumerators. This article will show you the steps in configuring the SSIS ForEach Loop File Enumerator.

It is used to enumerate files present in the specified folder. SSIS ForEach Loop File Enumerator will also traverse the subfolder present in the specified folder. For example, if you want to move multiple files from one folder to another or upload multiple files to the FTP server using the FTP task, etc. Before we step into package creation, let us see the source data.

We have a Folder inside the Documents drive, and Our task is to load data from text files to the SQL Server table. The below screenshot shows you the files inside that folder.

The following screenshot will show you the data inside Employee1.txt.

The below screenshot will show you the data inside Employee2.txt.

The below text shows the data inside Employee3.txt.

The following screenshot shows the data in the Employee4.txt.

SSIS ForEach Loop File Enumerator

In this SSIS ForEach Loop File Enumerator example, we will enumerate those Employee text files, extract their data, and load them into the SQL table. To do so, First Drag and drop the Foreach Loop Container into the Control Flow region.

Double click on it will open the SSIS Foreach Loop Editor to configure it. Within the General tab, Please specify a valid and more meaningful Name and Description.

Before stepping into the remaining properties, we must understand the Enumerator property. From the screenshot below, you can observe that we are selecting SSIS Foreach File Enumerator because we want to loop over the texts present in our system. Next, click the Browse button to select the folder name from our file system.

Reminig Iterations are:

• ADO Enumerator
• ADO.NET Schema Rowset Enum
• Item Enumerator
• NodeList Enumerator
• SMO Enumerator
• Variable Enumerator

The screenshot below shows that we are selecting the FILE SYSTEM TASK FOLDER folder in our E Drive. Click the OK button to select it.

Within the Files section, we have to specify the type using the Wildcards. In this example, we want to enumerate text files, so we are using Wildcard *.txt

TIP: If you’re going to work with all extensions, then use *.* wildcard

The list of properties under the Retrieve File Names option in SSIS ForEach Loop File Enumerator are:

• Name and Extension: If you select this option, it will store the File name and extension in the variable. For example, Employee1.txt
• Name Only: If you choose this option, the Foreach loop will store the Filename in the variable. For instance, Employee1
• Fully Qualified: If you select this option, it stores Path, Filename, and extension in the variable. For example, E:\Folder Name\Employee1.txt. From the below screenshot, you can observe that we are selecting this option.
• Traverse subfolders: If you want to check the subfolders, please checkmark this option

Next, we have to store the file names in a variable to use them in our Data Flow Task. To do that, go to the Variable Mappings section and select the user variable (if any) or create a new variable.

Once you click the <New Variable..>, the Add Variable window will open to create a new variable. In this example, we require variables to store the File path, Name, and extension. So, we are creating a string variable and assigning the name as EmployeeFile_Name. Click OK to finish creating a string variable.

Now select the newly created variable and assign it to index 0. Once completed, click OK to finish configuring the Foreach Loop Container.

Next, Drag and drop the Data Flow Task from the toolbox to the Foreach loop container and rename it SSIS Foreach Loop File Enumerator.

Double click on it will open the data flow tab. Next, Drag and drop Flat File Source and OLE DB Destination.

Double click on Flat File Source will open the Editor to configure the connection manager settings. If you haven’t created Flat File Connection Manager, click the New button to open the Connection Manager Editor. From the below screenshot, you can observe that we are selecting Employee1 from our System Task folder.

Our text files hold Column names as the first row, so please checkmark the Column names in the first data row option.

Click on the columns tab to preview the column data and alter the row and column delimiters.

Click the OK button to close the Flat File Source Connection Manager Editor, and click on the Editor columns tab to verify the columns. In this tab, we can uncheck the unwanted columns.

Double click on OLE DB Destination and select the OLE DB Connection Manager, and select the [SSIS ForEach Loop File numerator] table present in the Database to insert the new records.

Click on the Mappings tab to check whether the source columns are exactly mapped to the destination columns.

Before executing the SSIS ForEach Loop File Enumerator task, we have to change the Connection string settings of a Flat File Connection Manager. Because, up to now, we have configured the Flat File connection manager to select a single file (i.e., Employee1.txt). But our task is to choose Employee1, Employee2, Employee3, and Employee4. It means we have to assign the File names dynamically. And to do this, we have to alter the connection manager setting.

Please select the Flat File Connection Manager from the Connection Managers pane, and right-click on it will open the Context menu. Please choose the Properties option.

Click on the Expressions property will open the Property Expressions Editor. Here we have to configure the Connection String settings. So, select the Connection Manager property from the drop-down box and click on the … button.

Once you click on the … button, it will open the Expression Builder to build the expression. Here we are dragging the EmployeeFile_Name variable as an expression because it holds the Full path of every text file present in the system folder.

Click OK to finish configuring the SSIS ForEach Loop File Enumerator package. Let us Run the package.

Let us open the SQL Server management studio and check whether we inserted the records from multiple text files into the destination table using SSIS ForEach Loop File Enumerator or not.