SSIS Exclude certain files in Foreach Loop Container

Question

I have a SSIS package that loads csv files from a particular directory, using an expression and a wildcard for picking up all the files.

Currently, the FileSpec of the container looks for all files with the following format:

fileName_environment_*

and this is working fine. A second set of files are now bing loaded from the same directory, and to distinguish these, the file format is:

fileName_environment_business_*

So, the second SSIS package picks up only the new files since the structure of the files will look like:

filename_environment_abc 
filename_environment_def
filename_environment_xyz 
filename_environment_business_abc
filename_environment_business_def 
filename_environment_business_xyz

but the first package will process all files.

So, the question is, can I set the FileSpec of the first SSIS package to ignore the files which are in the format:

fileName_environment_business_*

Demobilize

Do you have to run them in the order specified? In my eyes running the package with the smallest/most specific scope first seems like the best thing to do. But If you rely on data from the other files this is not doable. – Cadency

@Cadency The packages don't remove the files - they just read the data. So, running in a certain sequence will make no difference. – Demobilize

Aha. So there is no archiving on files that has been read? This can easily be implemented by a File System Task. Then you don't have to read the same file several times (unless that's what you want ofc) and by running the "business" package first you will get what you want. I don't know what you are allowed/not allowed to do, but this can be a nice way of getting it done. – Cadency

@Nighty_, we can only read the files unfortunately. – Demobilize

Answer 1

In the foreach loop container put a dummy Script task before the first block you've got already. Connect those two with a line and set Constraint Options to expression, in which you should define a FINDSTRING function:

FINDSTRING(@var, "business", 1) == 0

Where @var is the loop iterable.

Only files without "business" inside will proceed to the next step. Hope this is what you wanted.

Declination

Thanks, I considered a script task too, but just wanted to see if there were other ideas. I'll wait to see if there are other solutions before deciding the approach. – Demobilize