Posted - 02/20/2013 : 10:34:03
| I created a basic SSIS package using SSDT 2012.
It has an execute sql task and an OLEDB connection manager. I set the execute SQL task to used the OLEDB connection manager and used direct Input to put the sql query
Exec [dbo].[error_handler] 99999
If I run this sql using ssms connected to the same database as the OLEDB connection manager in the SSIS package, it runs fine.
But, when I run the package I get this error:
[Execute SQL Task] Error: Executing the query "Exec [dbo].[cz_error_handler] 99999" failed with the following error: " ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Then I tried to use the query builder and used the same sql and hit Verify SQL button, i get the following:
The SQL has been verified against the data Source. I hit ok there, I get the following error:
The EXEC SQL construct or statement is not supported.
The strange thing is that the Exec statement runs and writes a row in the Db as it is supposed to, but fails the task. what am I missing?
Thank you for your help.