In this article, we are going to look into steps to loop though each file under a folder using ForEach file Enumerator.
Introduction
Foreach File Enumerator is to loop through the files in a directory/folder/sub
folders. This will be very useful to store each file name of folder &
subfolders into a table for Audit purpose; loading flat files into staging
database;
Scenario:
In the Data warehousing, the huge amounts of data will be Ftp’d/dumped
to some network file paths / some fixed directory of one server in the form of
files. These files should be dumped to some staging tables first, then load
them to original tables of destination server.
This enumerator is to look into each of file in a folder (it can be a
dynamic directory path); the file names can be of fixed format and have some
specific extension such as .csv or .txt
Below are the steps to create a package for looping each file in a
dynamic folder path; read the file data and save it to database table
Here, am going to showcase the looping of each
file in a folder/directory and then displaying file names in message box.
- Create package by right-click on SSIS Packages folder in a project of
Solution Explorer
- Name it as “Foreach Loop File Enumerator.dtsx”.
Drag “Foreach Loop Container” which is available on “SSIS Toolbox” onto the Control
Flow of package working space.
- Create two SSIS Variables from SSIS menu, name those as varFileName
& varFolderNam
Here, the Directory path is dynamical. The
values to the variable User::varFolderPath can be passed from command prompt by
configuring package variables.
Click on “Edit
Script” and write down below code to display a message box with File Name
in the Folder path
public void
Main()
{
// TODO: Add your code here
MessageBox.Show(" File Name is " + Dts.Variables["User::varFileName"].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
}
Output: Run the above
package by clicking F5 function key… 
Conclusion
In this way we can
utilise Foreach File Enumerator to loop through the files in a folder, network
file share path or a directory. You can traverse through the subfolders too by
checking the
Traverse Subfolders” option in the Foreach Loop Editor.