Execute SQL Task Error: Executing the query failed with the following error: “Incorrect syntax near ''.”

Question

I am working on a SSIS package that rejects already loaded files & load only new files to table. I used for each loop and exceute SSQL to validate if the files are already loaded. When I evaluate the expression of Execute SQL Task, it evaluates fine. But When I run the paackage I get the following error.

[Execute SQL Task] Error: Executing the query "DECLARE @FileName VARCHAR(100) SET @FileName=Custo..." failed with the following error: "Incorrect syntax near ''.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The Expression I used in the Execute SQL task is :

"DECLARE @FileName VARCHAR(100) SET @FileName="+@[User::FileName]+"' IF EXISTS (SELECT 1 FROM [dbo].[FileLoadStatus] WHERE filename =@FileName) BEGIN SELECT 1 AS FileExistsFlg END ELSE BEGIN Select 0 AS FileExistsFlg END"

screen shot of the execute SQL Task

I really apprecaite if you can tell where the problem is ?

I see a double pair of commas "" after @[User::FileName]+... I think it should be only one " — Gi1ber7

Adán Bucio Adán Bucio · Accepted Answer · 2020-10-25T07:21:10

You could simplify your expression a little bit to make clear where the SSIS variable is being used:

"SELECT COUNT(*) AS FileExistsFlg 
FROM (
    SELECT TOP(1) * 
    FROM
        dbo.FileLoadStatus
    WHERE
        [filename] = '" + @[User::FileName] + "'
) x;"

On the other hand for the SQL Task you could use a standard parameterized query. Assuming you are using an OLEDB connection, the parameter placeholder is the ? sign. No expression is needed and the equivalent Direct Input for the task is:

SELECT COUNT(*) AS FileExistsFlg 
FROM (
    SELECT TOP(1) * 
    FROM
        dbo.FileLoadStatus
    WHERE
        [filename] = ?
) x;

With OLEDB you have to map your variable to the placeholder by position (zero based) so in this case the Parameter Name is the number zero. The other properties depend on your metadata and correspond to the variable you would have declare in SQL...

This is less error prone, clearer and reusable for multiple calls as it generates a Prepared Statement.

If your connection type was ADO.Net, the mapping is name based. So check the documentation for the Parameter names and markers for each connection type.

Execute SQL Task Error: Executing the query failed with the following error: “Incorrect syntax near ''.”

1 Answers