SSIS ForEach Loop File Enumerator

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

SSIS Foreach File Enumerator: This 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 uploading multiple files to the FTP server using the FTP task, etc. Before we step into the package creation, let us see the source data.

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

SSIS ForEach Loop File Enumerator 0

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

SSIS ForEach Loop File Enumerator 1

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

SSIS ForEach Loop File Enumerator 2

The below text file shows the data inside the Employee3.txt file.

SSIS ForEach Loop File Enumerator 3

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

SSIS ForEach Loop File Enumerator 4

SSIS ForEach Loop File Enumerator

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

SSIS ForEach Loop File Enumerator 5

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

SSIS ForEach Loop File Enumerator 6

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

SSIS ForEach Loop File Enumerator 7

From the below screenshot, you can observe that we are selecting the FILE SYSTEM TASK FOLDER folder in our E Drive. Click the OK button to select it.

SSIS ForEach Loop File Enumerator 8

Within the Files section, we have to specify the file 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

List of properties under Retrieve File Names option in SSIS ForEach Loop File Enumerator are:

  • Name and Extension: If you select this option, the Foreach loop will store File name and extension in the variable. For example, Employee1.txt
  • Name Only: If you choose this option, the Foreach loop will store Filename in the variable. For instance, Employee1
  • Fully Qualified: If you select this option, the Foreach loop 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
SSIS ForEach Loop File Enumerator 9

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

SSIS ForEach Loop File Enumerator 10

Once you click on 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, File Name, and its extension. So, we are creating a string variable and assigned the name as EmployeeFile_Name. Click OK to finish creating a string variable.

SSIS ForEach Loop File Enumerator 11

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

SSIS ForEach Loop File Enumerator 12

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

SSIS ForEach Loop File Enumerator 13

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

SSIS ForEach Loop File Enumerator 14

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

SSIS ForEach Loop File Enumerator 15

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

SSIS ForEach Loop File Enumerator 16

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

SSIS ForEach Loop File Enumerator 17

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

SSIS ForEach Loop File Enumerator 18

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

SSIS ForEach Loop File Enumerator 19

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

SSIS ForEach Loop File Enumerator 20

Before we start executing the task, we have to change the Connection string settings of a Flat File Connection Manager. Because, up to now, we configure the Flat File connection manager to select the single file (i.e., Employee1.txt file). 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.

SSIS ForEach Loop File Enumerator 21

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.

SSIS ForEach Loop File Enumerator 22

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 File system task folder.

SSIS ForEach Loop File Enumerator 23

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

SSIS ForEach Loop File Enumerator 24

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

SSIS ForEach Loop File Enumerator 25

Comments are closed.