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.