Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Execute sql task fails, but sql runs

Author  Topic 

learnsql123
Starting Member

23 Posts

Posted - 2013-02-20 : 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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-20 : 10:42:42
you need to set resultset property to full resultset for execute sql task as sp returns a resultset


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -