CODEWITHSUNDEEP
sql-serverssisetl
Dizzy49
Dizzy49

Reputation: 1530

Cannot Get Constraint to Work on SQL Task in ForEach Loop

I am trying to perform a pretty basic process:

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:

enter image description here

Variables:

enter image description here

Here's the ForEach Loop Setup:

enter image description here enter image description here

'File Exists in Table' SQL Task:

enter image description here enter image description here enter image description here

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:

enter image description here

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:

enter image description here enter image description here enter image description here

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

enter image description here

What the hell am I doing wrong?!?!

Thanks!!

Upvotes: 3

Views: 730

Answers (3)

Yonas
Yonas

Reputation: 50

Have you tried changing the ResultName id to 0.

You can take a look at the documentation for reference. enter image description here

Upvotes: 1

Dizzy49
Dizzy49

Reputation: 1530

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!

Upvotes: 0

Hadi
Hadi

Reputation: 37368

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.

Upvotes: 0

Related Questions