In this article, we are going to look into steps to loop though each file under a folder using ForEach file Enumerator.
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
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
- 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
Here, the Directory path is dynamical. The
values to the variable User::varFolderPath can be passed from command prompt by
configuring package variables.
Output: Run the above
package by clicking F5 function key…
Click on “Edit
Script” and write down below code to display a message box with File Name
in the Folder path
// TODO: Add your code here
MessageBox.Show(" File Name is " + Dts.Variables["User::varFileName"].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
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
Traverse Subfolders” option in the Foreach Loop Editor.