The SSIS ForEach Loop container will repeat the control flow task for N number of times, which is similar to Foreach loop in any programming language. The SQL Server Integration Services provides 8 types of enumerators and In this article we will show you the steps involved in configuring the SSIS ForEach Loop File Enumerator.
File Enumerator: This is used to enumerate files present in the specified folder. It will also traverse the sub folder present in the specified folder. For example, if you want to move multiple files from one folder to other or uploading multiple files to FTP server using 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. Below screenshot shows you the files inside that folder.
Following screenshot will show you the data inside the Employee1.txt file.
Following screenshot will show you the data inside the Employee2.txt file.
Below screenshot will show you the data inside the Employee3.txt file.
Following screenshot will show you the data inside the Employee4.txt file.
SSIS ForEach Loop File Enumerator
In this example, we will enumerates those Employee text files and extract the data present in that files and load them into SQL table. In order to do so, First Drag and drop the Foreach Loop Container into the Control Flow region as shown below
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 FILE SYSTEM TASK FOLDER folder in our E Drive. Click 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 want to work with all extensions then use *.* wildcard
List of properties under Retrieve File Names are:
- Name and Extension: If you select this option, Foreach loop will store File name and extension in the variable. For example, Employee1.txt
- Name Only: If you select this option, Foreach loop will store File name in the variable. For example, Employee1
- Fully Qualified: If you select this option, Foreach loop will store Path, File name 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 sub folders then Please check mark this option
Next, we have to store the file names in a variable so that we can use them in our Data Flow Task. In order to do that, go to Variable Mappings section and select the user variable (if any) or create new variable as we shown below
Once you click on the <New Variable..>, Add Variable window will be opened to create new variable. In this example, we require variable to store the File path, File Name and its extension so we are creating string variable and assigned the name as EmployeeFile_Name. Click OK to finish creating string variable.
Now select the newly created variable and assign it to index 0 as we shown in below screenshot. Once you are done, Click OK to finish configuring the Foreach Loop Container.
Next, Drag and drop the Data Flow Task from toolbox to 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 from toolbox to data flow region.
Double click on Flat File Source in the data flow region will open the Flat File Source Editor to configure the connection manager settings. If you haven’t created Flat File Connection Manger before click on the New button will open the Flat File Connection manager Editor. From the below screenshot you can observe that, we are selecting Employee1 text file from our File System Task folder.
Our text files hold Column names as the first row so, Please check mark 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 also.
Double click on OLE DB Destination will open the OLE DB Destination Editor. 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 Mappings tab to check whether the source columns are 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 select 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 Manger from the Connection Managers pane and right-click on it will open the Context menu. Please select the Properties option as we shown below.
Click on the Expressions property will open the property Expressions Editor. Here we have to configure the Connection String settings. So, select the Connection manger property from drop down box and click on the … button as shown below.
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 expression. Because, it holds the Full path of each and 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
Thank You for Visiting Our Blog