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 provides eight types of enumerators, and 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.
The following screenshot will show you the data inside the Employee1.txt file.
The below screenshot will show you the data inside the Employee2.txt file.
The below text file shows the data inside the Employee3.txt file.
The following screenshot shows the data in the Employee4.txt file.
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
Double click on it will open the Foreach Loop Editor to configure it. Within the General tab, Please specify valid and more meaningful Name and Description.
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.
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.
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
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
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.
Now select the newly created variable and assign it to index 0. Once you 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 as 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 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.
Our text files hold Column names as the first row so, Please checkmark the Column names in the first data row option
Click on columns tab to preview the column data and alter the row and column delimiters.
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.
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.
Click on the Mappings tab to check whether the source columns exactly mapped to the destination columns.
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.
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 File system task 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
