ForEach loop–file enumerator

Foreach File Enumerator

ForEach loop enumerator Iterate through given folder. From the following example, I am going to iterate through location “C:\Users\gowdhdhan\Desktop\SampleData\SrcFiles”. I have mixture of Excel and txt files inside my source the folder.

Files configuration:

Below screenshot, I have highlighted where to configure the file filter.

Following table illustrate the filter configuration.

Filter option	Description
*.*	Searches all the files. Including Excel file and Text file
*.txt	Searches only text file
Sam*.*	Searches all the files which starts with File name “Sam”.
Sam*.txt	Searches only text file which starts with File name “Sam”.
*Data.xls	Searches only excel file which ends with File name “Data”
*Data*.txt	Searches all text files and file name contains “Data”

Retrieve file Name configuration: 

There are three options available for retrieve file configuration. We need to carefully select depend on the requirement. Following table illustrate configuration and output sample.

Configuration	Output sample
Fully qualified	C:\Users\gowdhdhan\Desktop\SampleData\SrcFiles\Sample Data.txt
Name and Extension	Sample Data.txt
Name only	Sample Data

If we are using the file archiving strategy and have extension in another folder then we can use the “Name only” option. Variable mapping should be very important to save the “Fully qualified” path or “Name and Extension” or “Name only”. Following screenshot illustrate variable mapping. Index is always 0 for “ForEach File enumerator”.

Variable mappings and Index usage:

How to test and retrieve what is saved in variable?

We can use the “Script component” to test. We can use “FileName” variable. We can select in ReadOnlyVariables and ReadWriteVariables based on the requirement.

Following code [void main() method] to test the how variable mapping is working.

   1:    public void Main()

   2:          {

   3:              var fileName = Dts.Variables["FileName"].Value.ToString();

   4:   

   5:              var path = string.Format("From FileName variable : [{0}]",fileName);

   6:   

   7:              MessageBox.Show(fileName);

   8:   

   9:              // TODO: Add your code here

  10:              Dts.TaskResult = (int)ScriptResults.Success;

  11:          }

 

running the SSIS package you will get the following screen. It will be iterated based on the number of file. I have given first screen shot.

 

With Fully qualified:

 

 

With Fully qualified:

Name only:

Based on the requirement we can use the “Retrieve file name” and use it accordingly. I will write separate post on archiving file strategy.

Thanks for reading

-Gowdhaman