Friday 12 September 2014

Foreach loop with *.xls wildcard also returns *.xlsx files

Case
I have a Foreach Loop Container with a file enumerator. The wildcard is *.xls, but it also returns *.xlsx files. How do I prevent that?

Loop through *.xls also includes xlsx files





















My xls loop includes xlsx and xlsm files


















Solution
This is actually similar to the DIR command in a DOS/Command Prompt.
All xls files? (/b is to remove my Dutch header/footer)











The workaround is simple. And if you don't like the solution then you could use my Sorted File Enumerator that also supports regular expression wildcards.

1) Dummy
Add an empty/dummy task or Sequence Container in your Foreach Loop Container. And connect it to your first task.

Empty/collapsed Sequence Container added

























2) Precedence Constraint Expression
Add an expression on the Precedence Constraint between the dummy and your first task. It should look something like LOWER(RIGHT(@[User::FilePath], 4)) == ".xls" (replace the variablename and/or file extension).

Expression with LOWER and RIGHT to check the file extension

















3) The result
Now test the package (Replace my example Script Task with your own tasks).
The result: only two xls files and no xlsx or xlsm files





















No comments:

Post a Comment

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...