Foreach File Enumerator in SSIS

Overview

Foreach loop container implementation is similar to foreach loop structure in programming languages. SSIS provides 7 types of enumerators with foreach 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, etc.
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.

In this article, I am explaining Foreach File enumerator.

Foreach File Enumerator

The file enumerator loops through a collection of files within a folder and makes it possible to execute 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 text file to SQL Server database table. See the below screen of source folder to get more idea.

Implementation Overview

We will be using a data flow task inside the Foreach Loop Container. 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 value of the file path in each iteration, we have 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 loop Container.

Foreach Loop Container

Drag and drop a Foreach Loop Container into the control flow and double click on it. Navigate to 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 files under input folder, you can give *.*. 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 have created earlier.

Data Flow Task

Add a dataflow task inside Foreach Loop Container and navigate to its dataflow tab. Since the source is text file, our source will be 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 the design purpose and later we can change this to expression to make use of the file path from the variable.

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

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 connection string as an expression.

Package Execution

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

Conclusion

Hope you got a clear idea of the implementation of Foreach File enumerator which is very helpful in file parsing to databases.

Thank you for reading.