Search code examples
sqlsql-serverssisetlexecute-sql-task

SSIS Precedence Constraint Not Working Due to Path Formatting

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!

enter image description here

enter image description here

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

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