Tim Mitchell is a business intelligence consultant, author, trainer, and SQL Server MVP with over a decade of data management experience. He is a partner with Linchpin People and is the principal of Tyleris Data Solutions.

Tim has spoken at international and local events including the SQL PASS Summit, SQLBits, SQL Connections, along with dozens of tech fests, code camps, and SQL Saturday events. He is coauthor of the book SSIS Design Patterns, and is a contributing author on MVP Deep Dives 2.

In a nutshell, the SSIS foreach loop will enumerate a given list of items (files in a directory, nodes in an XML file, static list of values, etc.) and will perform some operation for each of the items in the collection. This behavior is similar to foreach loop constructs that are found in most high-level programming languages. These programming elements normally include a continue statement, which allows you to stop processing of the current item and move on to the next one. Jack hoped to find this capability built into the SSIS foreach loop, but unfortunately it doesn’t exist natively.

However, there is a workaround that will allow you to reproduce the continue statement in the SSIS foreach loop. By including a “dummy” data flow within the foreach loop, we can use a precedence constraint to skip some of the items in the list based on the value of a variable. To demonstrate, I’ll use a foreach loop to make a copy of all of the files in a given directory except for one specific file. I'll first set up the foreach loop in the control flow pane to enumerate over the files in a particular directory, by using the Foreach File Enumerator setting:

Now, within the foreach loop above, I create two data flows; one that does nothing at all, and the other that reads the current file in the list and writes the data out to a new file, the name of which is set using an expression.

Next comes the important part: I create a precedence constraint from my dummy data flow to the one that actually performs the work, and I’ll edit the expression to exclude one of the file names:

In this scenario, the package flow will only move from the dummy data flow to the one containing our business logic if the variable value, in this case the current filename, does not match the one we intend to skip.

So the short answer is that the SSIS foreach loop doesn’t have native capability to skip processing for certain values, but using a do-nothing container and a precedence constraint within the loop is a creative and effective substitute.

Comments

Posted by Steve Jones on 16 September 2009

Nice trick. Hadn't thought about that before. How complex can the expression be? Can we use RegEx in there to skip, say all files that have "10" in them, if I had a bunch of files that were named "20090910.txt", "2001032.txt", etc.?

Posted by Steve Jones on 16 September 2009

Nice trick. Hadn't thought about that before. How complex can the expression be? Can we use RegEx in there to skip, say all files that have "10" in them, if I had a bunch of files that were named "20090910.txt", "2001032.txt", etc.?

Posted by Tim Mitchell on 16 September 2009

You can get very complex with the expressions. There's no RegEx per se, but you can use expression functions such as FINDSTRING() to determine if the value matches a specific pattern.

The only downside is that, unlike some other controls, there's no expression editor attached to the precedence constraint. You can still use the full power of the expression language, but you'll have to write it purely by hand without the editor.

Posted by knight_devin@hotmail.com on 16 September 2009

Awesome tip Tim! I've used dummy tasks to evaulate expressions on precedence constraints before but had never thought to use them as part of a ForEach Loop.

Posted by douglashwilliams on 23 September 2009

Where was this 6 months ago?! I could have definitely used this to resolve an issue...instead I had to resort to large doses of electro-shock therapy.

Thanks. I'll use your tip/solution very soon.

Posted by Scott Coleman on 23 September 2009

If you needed a RegEx or other complex expression, use a script task in place of the empty data flow to do the computation and set a package variable. Then use the variable in the precedence constraint.

Posted by MTY on 21 December 2009

Tim,

i like your articule. I just dont find a way to compare my existing file. I need to insert my files into a table, but before that I need to see if the file exist in the table already, so i wont process the rest of the package.

i tried to do what you mention, but in my data flow(dummy) I added an OLE DB Source to read the FileName (fiel of the table) and then I tried to do the expression validation but i get errors.