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.
Neat trick, thanks for sharing
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).
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)
I would move the files to an archive folder using a File System Task in the loop so they are not there the next time the package runs.