How to loop through files in SSIS

Most of the SSIS developers want to get the list of the files from a folder (and maybe subfolders) and save them in to an object variable or loop though them one by one so that they can use it in an object within SSIS. (e.g. *.xml, *.bmp, *.txt, most of all Excel files *.xls and etc...)
This scenario explains how to loop though the files one by one.



First step is to define few variables in the system



1. uVar_FolderNameOfFilesToBeProcessed : Points to the folder that is required to be processed.
2. uVar_SourceConStr_DataSource : File name and folder gets saved in on each loop
3. uVar_SourceFileExtention : Defines the file extension type

Next is the settings of the Loop

First ---


Second --


Next step the script setting

In this section I am going to make it easy, so I am going to use a message box to display the File and folder name.



Please Note: SSIS is not a front-end software, the message box is just for test purposes.
next click on the "Design Script" and enter the code....

Public Sub Main()
'
' Add your code here
'
MsgBox(Dts.Variables("uVar_SourceConStr_DataSource").Value.ToString)
Dts.TaskResult = Dts.Results.Success

End Sub



Testing Scenarios

Assuming that we have 2 types of file (1- Excel files 2- Text file) .....


---First test finding *.xls files
In the variable "uVar_SourceFileExtention" we have "*.xls", and in the folders and subfolders we have 2 excel files, once running the SSIS package from BIDS you'll get 2 message box one message for each loop as...

---Second test finding *.txt files
Change the variable "uVar_SourceFileExtension" to "*.txt", and run the package again in BIDS, you will have to get only one message.

---Third test finding files with a special extension *30.xls or File*.xls
In this case you will get the same message box as the first scenario except you will only get the first message, file “File20070930.xls”