SSIS For Loop Skip Files

When running a For Each Loop through a set of files, sometimes you will have specific files that you do not want to load.

For example, I have a set of files named:


If I want to skip the file that starts with “W” then I will need an expression in my For Each Loop to detect this file.

Inside the For Each loop I am going to place a sequence container. This will give me a place to anchor my expression which I will place on the precedence constraint coming from the sequence container. There are no tasks in the sequence container.


On the precedence constraint line I am going to set it to constraint and expression. The expression will be:

substring(Upper(@strFileName),1,1) != “W”


This is looking at the first letter in the filename and comparing it to the letter “W”. I would place the “W” in a variable and use that instead, I am just showing this way for simplicity. Notice I convert the file name variable to upper case and compare it to an uppercase “W”. That way the case will not matter.

This entry was posted in SSIS, Syndication and tagged . Bookmark the permalink.

4 Responses to SSIS For Loop Skip Files

  1. BigPhil says:

    Neat trick, thanks for sharing

  2. Phil H says:

    Nice tip, Mike. I use it to inspect the date of a file while loading data from legacy files with differing schema (e.g. someone added a field to a table on a certain date).

  3. SQLDevSiva says:

    very Well,
    But I need one more tip, Let me give you a scenario where I stuck, I have a set of files in a folder, I need to process through ForEachLoop, but if any Error happends it should be ended. (upto this point all is well), My Question, Could you please tell me how can I achieve to skip the files which were already processed in the Earlier Loop while executing the package for second time.

    Many Thanks in Advance,
    Prabhusivaraman, (Bangalore, India)
    SQL Developer,

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s