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!!

Have you tried checking the variable values during execution? For example, adding a break point and then viewing the variables in the locals window? — userfl89
– userfl89, Commented May 24, 2019 at 14:28
@beibeizhu Using /@User::id gives a validation error. Using /@[User::id] validates, but still has the same issue. — Dizzy49
– Dizzy49, Commented May 24, 2019 at 18:09
@userfl89 - Yes, the User:CurrentFileName looks like \\\\erpsql\\p21shares\\purchasingsave\\item_3266_brianh.txt and the User::id never changes from 0 — Dizzy49
– Dizzy49, Commented May 24, 2019 at 18:10

Yonas · Accepted Answer · 2019-05-29 15:06:41Z

1

Have you tried changing the ResultName id to 0.

You can take a look at the documentation for reference.

edited May 29, 2019 at 15:06

answered May 24, 2019 at 11:24

Yonas

502 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hadi · Accepted Answer · 2019-05-24 21:48:53Z

0

Change Input Parameter size

Try increasing the input parameter size in Execute SQL Task, since 100 may cause truncation. For a file path i will set it to 1000.

Using Expressions

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

When using expression, the SQLStatement in the General Tab don't have any value since it is changed at runtime.

answered May 24, 2019 at 21:48

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Comments

Dizzy49 · Accepted Answer · 2019-05-26 06:17:52Z

0

It ended up being a few things.

The constraint did need to use @[User::id] == 0
I needed to use @[User::CurrentFileName] in the Query in the SQL Task
I did NOT need the REPLACE in the Query

Thank you, without the multitude of ideas I may not have figured it out!

answered May 26, 2019 at 6:17

Dizzy49

1,5502 gold badges28 silver badges50 bronze badges

Collectives™ on Stack Overflow

Cannot Get Constraint to Work on SQL Task in ForEach Loop

3 Answers 3

Comments

Change Input Parameter size

Using Expressions

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Change Input Parameter size

Using Expressions

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related