Click here to Skip to main content
15,900,816 members
Articles / Programming Languages / SQL

Foreach File Enumerator in SSIS

Rate me:
Please Sign up or sign in to vote.
4.82/5 (10 votes)
1 Aug 2014CPOL3 min read 139.8K   7   19
Foreach file enumerator in SSIS


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.


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.


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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Software Developer (Senior)
India India
I am a software Engineer specialized in Microsoft technologies(Asp.Net, SQL Server, MSBI etc).

Comments and Discussions

Questionforeach loop does not have enumerator configuration appearing Pin
tahir ur rehman19-May-18 7:44
tahir ur rehman19-May-18 7:44 
QuestionError with Variable & Connection Manager Pin
Member 1300010213-Feb-17 23:26
Member 1300010213-Feb-17 23:26 
GeneralMy vote of 5 Pin
Member 117119731-Apr-16 10:57
Member 117119731-Apr-16 10:57 
QuestionFor Each file enumerator does not allow DFS share Pin
Member 94941934-Feb-16 9:00
Member 94941934-Feb-16 9:00 
GeneralMy vote of 5 Pin
James McCullough1-Oct-15 11:34
professionalJames McCullough1-Oct-15 11:34 
GeneralRe: My vote of 5 Pin
Member 117119731-Apr-16 10:58
Member 117119731-Apr-16 10:58 
QuestionThis was a tip... Pin
OriginalGriff16-Aug-14 23:01
mveOriginalGriff16-Aug-14 23:01 
AnswerRe: This was a tip... Pin
Sreekanth Mohan17-Aug-14 1:53
professionalSreekanth Mohan17-Aug-14 1:53 
GeneralRe: This was a tip... Pin
OriginalGriff17-Aug-14 2:14
mveOriginalGriff17-Aug-14 2:14 
GeneralRe: This was a tip... Pin
Sreekanth Mohan17-Aug-14 3:03
professionalSreekanth Mohan17-Aug-14 3:03 
GeneralRe: This was a tip... Pin
OriginalGriff17-Aug-14 3:46
mveOriginalGriff17-Aug-14 3:46 
GeneralRe: This was a tip... Pin
Sreekanth Mohan17-Aug-14 3:57
professionalSreekanth Mohan17-Aug-14 3:57 
GeneralRe: This was a tip... Pin
Member 1234775623-Feb-16 19:52
Member 1234775623-Feb-16 19:52 
GeneralRe: This was a tip... Pin
OriginalGriff23-Feb-16 23:14
mveOriginalGriff23-Feb-16 23:14 
GeneralRe: This was a tip... Pin
Member 117119731-Apr-16 11:06
Member 117119731-Apr-16 11:06 
GeneralRe: This was a tip... Pin
OriginalGriff1-Apr-16 22:59
mveOriginalGriff1-Apr-16 22:59 
GeneralRe: This was a tip... Pin
Member 117119732-Apr-16 2:07
Member 117119732-Apr-16 2:07 
GeneralRe: This was a tip... Pin
OriginalGriff2-Apr-16 2:15
mveOriginalGriff2-Apr-16 2:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.