Foreach File Enumerator in SSIS

Overview

The For each loop container implementation is similar to a foreach loop structure in programming languages. SSIS provides 7 types of enumerators for each loop container.

1. Foreach File enumerator to enumerate files in a folder. The enumerator can traverse subfolders.
2. Foreach Item enumerator to enumerate items that are collections. For example, you can enumerate the names of executable and working directories that an Execute Process task uses.
3. Foreach ADO enumerator to enumerate rows in tables.
4. Foreach ADO.NET Schema Rowset enumerator to enumerate the schema information about a data source.
5. Foreach From Variable enumerator to enumerate the enumerable object that a specified variable contains, it can be an array, an ADO.NET DataTable, an Integration Services enumerator and so on.
6. Foreach Nodelist enumerator to enumerate the result set of an XML Path Language (XPath) expression.
7. Foreach SMO enumerator to enumerate SQL Server Management Objects (SMO) objects. For example, you can enumerate and get a list of the tables in a SQL Server database.

This article explains the Foreach File enumerator.

Foreach File enumerator

The file enumerator loops through a collection of files within a folder and makes it possible to execute something, for example a Data Flow Task, for each of the files without manually changing the connection string.

Scenario

The scenario is to load country specific customer data in a text file to a SQL Server database table. See the following screen of source folder to get a better idea.



Implementation Overview

We will be using a data flow task inside the Foreach Loop Container. The Foreach loop container will iterate through each file and each time the data will be loaded to the destination tables using the dataflow task.
To hold the vale of the file path in each iteration, we need to create a string variable.

Create variable

Right-click on the Control Flow and select variables. Create a variable of type string under the scope of the package or Foreach lopp Container.



Foreach Loop Container

Drag and drop a Foreach Loop Container into the control flow and double-click on it. Navigate to the collection tab on the left panel.

• Enumerator: Select Foreach File Enumerator to read files in the input directory
• Folder: Input folder where the country specific files resides
• Files: Specify the extension of the file to read. If you want to read all the files under the input folder, you can provide *.*. Since our input files are text files, we have provided *.txt.

Navigate to Variable Mappings tab to assign the file path in each iteration to the variable that we created earlier.



Data flow Task

Add a dataflow task inside the Foreach Loop Container and navigate to it's dataflow tab. Since the source is a text file, our source will be a Flat File Source. Drag and drop a Flat File Source into the dataflow and double-click on it.

Create a new connection manager by clicking on the new button. Here we need to provide an input file path for design purposes and later we can change this to an expression to make use of the file path from the variable.



Create an OLE DB Destination and point to the dataflow path towards the destination. Ensure that all source columns are pointed to the correct destination.



The final and most important step is to change the flat file connection path to the loop variable to read each file in the iteration. Right-click on the connection manager and select Properties.



Click on the expressions property and make a connection string as an expression.





Package Execution

Execute the package and verify the result in the destination table.



Conclusion

I hope you now have a clear idea of the implementation of the Foreach File enumerator which is very helpful for file parsing to Databases.

Thank you for reading.