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:

Abc.txt
Mno.txt
Rts.txt
Wln.txt
Xyz.txt

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.

clip_image002

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”

clip_image004

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.

About these ads
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,
    Accenture.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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