Cannot Get Constraint to Work on SQL Task in ForEach Loop

Question

I am trying to perform a pretty basic process:

• (Reset) Truncate Table
• Loop Through Folder of Files
• Check to See if FileName Exists in FILES Table
• If Not, Import Data from File Into DATA Table
• Insert FileName into FILES Table

I can get it to go through the folder of files, load them, and insert the filename into the table. However, I cannot get the constraint to work so that it ONLY loads the files that are not already in the FILES table.

Here is my overall setup:

Variables:

Here's the ForEach Loop Setup:

'File Exists in Table' SQL Task:

I tried:

SELECT COUNT(id) as id FROM PORG_Files WHERE filename = ?

I also tried a SqlStatementSource Expression of:

"SELECT COUNT(id) as id FROM PORG_Files WHERE filename = '" + @[User::CurrentFileName] + "'"

But I wasn't sure what to do with the SQLStatement under General while using the Expression.

Here is the Contstraint that always seems to be True:

I thought the issue was with the path formatting where the variable has double \s, but with the REPLACE to correct it, it isn't working.

For good measure, here is the 'Insert File into Table' SQL Task:

Files insert into the table just fine, here is how the table looks:

What the hell am I doing wrong?!?!

Thanks!!

Answer 1

Have you tried changing the ResultName id to 0.

You can take a look at the documentation for reference.