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

Extracting ZIP files using SSIS

Sometime client provides many Flat files\Excel files zipped into single zip file. There are many ways to unzip the files. We can use WinZip and WinRar,but they are not free. In this blog, we will see about how to use 7 zip for extracting the data. 7-Zip is free to use and support all compressed format .  It’s a open source, we have used in our customer environment.You can use the following link to download.

http://www.7-zip.org/

Download 7-Zip and install.

I am going to use Sample.zip file in location “C:\Users\gowdhdhan\Desktop\SampleData\SampleZipFiles\” for demo purpose. Execute process task will extract the source files to “C:\Users\gowdhdhan\Desktop\SampleData\SrcFiles” folder.

Execute Progress Task for running 7 Zip

• Create SSIS package and “Execute Progress Task” in control flow.
• Configure “Execute Progress Task” something as follows. (argument “e” stands for extract)

Executable	C:\Program Files\7-Zip\7z.exe
Arguments	e "C:\Users\gowdhdhan\Desktop\SampleData\SampleZipFiles\Sample.zip"
Working directory	C:\Users\gowdhdhan\Desktop\SampleData\SrcFiles

• Click “Ok”

Run the SSIS package. All the files are moved to “C:\Users\gowdhdhan\Desktop\SampleData\SrcFiles” location. Source files are ready and can be used for further processing. I will write about using “ForEach loop container” in separate post.

 

Thanks for reading .

-Gowdhaman