May 8, 2009

SSIS: ForEach Loop - Looping through multiple file types

It has been a while since my last post, and that is because things at home have been keeping busy after Diego, my 3rd child, was born few weeks ago. The good thing is that I finally manage to squeeze some time to write this quick post.

The ForEachLoop container is very functional when trying to process multiple files of the same type. You can easily a ForEach file enumerator and use a wildcard to, let’s say, process all files with a TXT extension by entering “*.exe” in “Files” property of the task like this:



But what if you need to specify more than one file extension? Something like: “*.txt, *.csv” will not work inside of the ForEach container. However, you could still accomplish this goal by looping through all files ( Files=*.* ) and then using a precedent constraint to evaluate whether the file has to be processed or not – Similar to this approach -.

Here is how you configure the precedence constraint:








Here is the expression I used in the precedence constraint:
UPPER(SUBSTRING(@FileName,FINDSTRING(@[FileName],".",1)+1,LEN(@FileName)))=="TXT" || UPPER(SUBSTRING(@FileName,FINDSTRING(@[FileName],".",1)+1,LEN(@FileName)))=="CSV"

Few things to note:
1. In this example, the @FileName Variable only stores the file name (not the path). If you store the path, then you have to modify the expression little bit to account for the path part.
2. The sequence container is empty. Its only function is to serve as placeholder to create the precedence constraint.
3. The ForEach loop container is looping through all files, so make sure you test this approach before implementing it against folders with too many files. Honestly, I don’t think there would be significant performance degradation.

Please let me know if you find this sample helpful, or feel free to share an alternative approach.

7 comments:

  1. Hi nice post,well my issue is that I have 10 differenct Excel file,.xls file. they have to be FTPed from a FTP sever location. THis file will be updated on monthly basis. This file will be going into the SQL tales,aisnt,the SQL table are created and inorder to populate the table we use this FTP file.

    ReplyDelete
  2. For more information on For each loop enumerators (Foreach ADO.NET Schema Rowset Enumerator using Excel Sheets) and programming integration service (SSIS) using C# .Net and VB .Net, please visit the below link:
    http://www.sqllion.com/2009/06/programming-foreach-loop-container-%e2%80%93-enumerating-excel-sheets/

    ReplyDelete
  3. Hi,

    Nice post. I have a case where I need to process 2 files from different paths. i will need to insert the data into the database. I have everything done, but I just dont know how to loop and find the 2 files from 2 different paths. no to mention that the paths contain more than 100 files. I only will take the recent one.

    Any idea?

    ReplyDelete
  4. Hi rafael, Its really being very helpful for me.....appreciate your thoughts and efforts....

    ReplyDelete
  5. Hi Rafel, can you please upload this sample SSIS package?

    ReplyDelete
  6. Thanks Rafael, this helped me too.

    ReplyDelete

I will love to hear your feedback and comments. Thanks.