Skipping Items in a Foreach Loop

Recently, my friend Jack Corbett asked a question on Twitter:

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 such as C# or Java. 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. Although there is no such continue statement in SSIS, skipping items in a foreach loop is possible with a little creative design.

In this post, I’ll describe how skipping items in a foreach loop in SSIS is possible, and how to enable this behavior.

Skipping Items in a Foreach Loop

By using a little creativity, 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.

Conclusion

To Jack’s question earlier, 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.