Execute SQL Task error -

Question

I get the following error when I call a stored proc within an execute sql task in SSIS. "Description: Executing the query "Exec up_CallXXX" failed with the following error: "Incorrect syntax near '13'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly." This is how it is set up:

ResulSet : None ConnectionType : OLE DB SQLSourceType : Direct input SQL Statement : Exec up_CallXXX IsQueryStoredProcedure : False BypassPrepare : True

The stored proc runs fine when I execute it through SSMS. Does anyone have an idea what is going on.

Thanks!

Answer 1

This isn't really a complete answer, but the quickest route to troubleshooting this problem is likely to be to capture the command that SSIS is actually trying to execute using a SQL profiler trace, since I think SSIS uses sp_executesql to carry out "Execute SQL" tasks.

Once you have the text of the query SSIS is running, you may find that you can replicate the error if you execute it in SSMS.

I've had this kind of problem when the session settings for my SSMS session are different from the session settings used by SSIS/SSRS. Given the error message, it might be worth checking the SET QUOTED IDENTIFIER values in both sessions - but this is a guess.