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. In this article, we will show you the steps involved 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 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 the Employee1.txt.
The below screenshot will show you the data inside the Employee2.txt.
The below text shows the data inside the 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 and extract the data present in them 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 we step into the remaining properties, we have to understand the Enumerator property. From the below screenshot, you can observe that we are selecting SSIS Foreach File Enumerator because we want to loop over the txts present in our 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 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 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 so that we can 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 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, Name, and its 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 you have 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 Editor to configure the connection manager settings. If you haven’t created Flat File Connection Manager, click on the New button will 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 [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 we start 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 configure 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.
Comments are closed.