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(?,'\\','\')

Answer 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] + "'" 

Answer 2

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”