Tim Mitchell
Follow Tim Mitchell on Twitter  Like Tim Mitchell on Facebook  Subscribe on YouTube  Connect on LinkedIn  Subscribe to the Data Geek Newsletter

Skipping Items in a Foreach Loop

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

Any way to continue' a For each file enumerator in SSIS if the file name == a variable?

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. 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:

Skipping Items in a Foreach Loop

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:

figure2

figure3

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.

[Cross-posted from SQL Server Central]

About the Author

Tim Mitchell
Tim Mitchell is a business intelligence and SSIS consultant who specializes in getting rid of data pain points. Need help with data warehousing, ETL, reporting, or SSIS training? Contact Tim here: TimMitchell.net/contact

2 Comments on "Skipping Items in a Foreach Loop"

  1. Thanks! That worked for me!

  2. Great! Thanks for the feedback.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: