SSIS Precedence Constraint Not Working Due to Path Formatting

Question

Using SSIS and MS-SQL Server 2012

I have a SQL Task executing:

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

It never returns anything except 0 because the SSIS filename looks like:

\\\\erp\\shares\\Save\\item_1168.txt

And the Filename in the Table Looks like:

\\erp\shares\Save\item_1168.txt

I don't think I want to insert the filename into the table like that, so how/where do I format so I can make the matches to get my constraint that depends on this to fire.

Thanks!

Ok, If I run this query in SQL Manager it works.

SELECT COUNT(id) as id FROM PORG_Files WHERE filename = REPLACE('\\\\erp\\shares\\Save\\item_1168.txt','\\','\')

When I put the equivilant into the SQL Task Editor for the SQLStatement, it still returns 0

SELECT COUNT(id) as id FROM PORG_Files WHERE filename = REPLACE(?,'\\','\')

What, exactly, is the expression that you are using? – J Weezy May 22 '19 at 18:31 — J Weezy, May 22 '19 at 18:31
@Dizzy49 is the issue solved? – Yahfoufi May 24 '19 at 10:07 — Yahfoufi, May 24 '19 at 10:07

score 1 · Answer 1 · answered May 22 '19 at 13:18

Are you just stored the file name directly in a variable? If you store the file name as the expression of a string variable instead, the output format will what you described. The result of the 4 forward slashes (\) will will be only 2, and the 2 \ will be a single one. This is because the the forward slash must be escaped in an SSIS expression. In the Expression field of the variable, click the ellipsis and enter the text inside double quotes to make this an expression such as in the example below.

“\\\\erp\\shares\\Save\\item_1168.txt”

The task is inside of a ForEach Loop. The variable is changing as it goes through the files. It's just that my SQL statement can't make the match due to the extra \s — Dizzy49, May 22 '19 at 21:31

score 1 · Accepted Answer · answered May 22 '19 at 21:28

1

Workaround - Expression

Try using expression instead of passing parameters:

In the Execute SQL Task, Go To Expression Tab, Add an expression for SQLStatementSource property as following:

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

answered May 22 '19 at 21:28

Hadi

36,233
13
65
124

Do I then need to edit the SQLSourceType and SQLStatement under the General? – Dizzy49 May 22 '19 at 21:35
@Dizzy49 keep it `direct input` – Hadi May 22 '19 at 21:57
@Dizzy49 when using expression, the SQLStatement in the General Tab don't have any value since it is changed at runtime – Hadi May 24 '19 at 21:42
@Dizzy49 anything new? – Hadi May 27 '19 at 00:55
It ended up being a combination of things, but using the expression was the thing that did it for me :) – Dizzy49 May 27 '19 at 10:03

SSIS Precedence Constraint Not Working Due to Path Formatting

2 Answers2

Workaround - Expression