SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Execute sql task fails, but sql runs
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learnsql123
Starting Member

USA
23 Posts

Posted - 02/20/2013 :  10:34:03  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/20/2013 :  10:42:42  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000