Unmatched Files Processing through ForEach Loop Container

Question

I have some processed and unprocessed files in my Source Folder and the file names of all the processed files are stored in a table. How can I match the files names of source folder and table prior to ForEach Loop Container and process only unmatched files.

Answer 1

The solution below is a bit elaborate but it's the best I could think of.

STEP 1: Create 2 Variables, both strings.

      a)CurrentFile: This will be used for your Foreach Loop Container collection value
      b)ToProcess: This will be used to map the result set an Execute SQL Task explained 
                 below

STEP 2: Add an Execute SQL Task into your Foreach Loop Container.

Configure Parameter Mapping as shown below:

Use the script below as your SQL Statement:

DECLARE @ToProcess VARCHAR(1)

IF NOT EXISTS(SELECT [FileNames] FROM [YourFilesTable] WHERE FileNames = ?)

SET @ToProcess = 'Y'

SELECT @ToProcess AS ToProcess

Set ResultSet to Single Row as shown below:

Configure Result Set as shown below:

On the Execute SQL Task, configure the Precedence Constraint as shown below:

Your Foreach Loop Container should look like below: