Foreach File Enumerator in SSIS

Bandi
Posted by in Sql Server category on for Advance level | Points: 250 | Views : 2530 red flag
Rating: 5 out of 5  
 1 vote(s)

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
  • Double-click on Foreach Loop Container and go to Collection tab; select Enumerator as “Foreach File Enumerator”


  • To pick each .txt file in a specific directory, click on the above red marked one ; then select @[User::varFolderName]  variable as the Expression for Directory property as shown below,



Here, the Directory path is dynamical. The values to the variable User::varFolderPath can be passed from command prompt by configuring package variables.

  • In the same way, we need to set the FileSpec property to pick only the text files…


  • Then the Collection tab of Foreach Loop Editor looks as follows


  • Go to “Variable Mapping” tab and specify User::varFileName mapping to the index 0


  • Drag a Script Task to see the filenames in a specified directory…


  • Double –click on the above Script task, set the ReadOnlyVariable as User:varFileName;


  • 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.

Page copy protected against web site content infringement by Copyscape

About the Author

Bandi
Full Name: Chandrika Bandi
Member Level: Platinum
Member Status: Member,MVP
Member Since: 7/23/2013 5:52:37 AM
Country: India
[B]Mark This Response as Answer[/B] -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif
http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Posted by: Sriharim on: 5/2/2015 | Points: 25
Hi,

Can u please give more information on how can values to the variable (User::varFolderPath )can be passed from command prompt.


Login to post response

Comment using Facebook(Author doesn't get notification)