Exec SQL Task: Capture return code of stored proc not working

I am just trying to capture the return code from a stored proc as follows and if I get a 1 I want the SQL Task to follow a failure(red) constrainst workflow and send a SMTP mail task warning the customer. How do I achieve the Exec SQL Task portion of this, i get a strange error message [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

Using OLEDB connection, I utilize SQL: EXEC ? = dbo.CheckCatLog

EXEC SQL Task Editer settings:
RESULTSET: Single Row
PARAMETER MAPPING: User::giBatchID
DIRECTION: OUTPUT
DATATYPE: LONG
PARAMETER NAME: 0

PS-Not sure if I need my variable giBatchID which is an INT32 but I thought it is a good idea to feed the output into here just in case there is no way that the EXEC SQL TASK can chose the failure constrainst workflow if I get a 1 returned or success constraint workflow if I get a 0 returned from stored proceedure

CREATE PROCEDURE CheckCatLog
@.OutSuccess INT
AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @.RowCountCAT INT
DECLARE @.RowCountLOG INT

these totals should match
SELECT @.RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT)
SELECT @.RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG)
--PRINT @.RowCountCAT
--PRINT @.RowCountLOG
BEGIN
IF @.RowCountCAT <> @.RowCountLOG
--PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file'
--RETURN 1
SET @.OutSuccess = 1
END
GO

Thanks in advance

Dave

Set ResultSet=None.

If OutSuccess is an OUTPUT parameter, you have to modify the second line in SP to "@.OutSuccess INT OUTPUT". If it is not an OUTPUT parameter, you have to modify the mapping direction in your task.

Also, if you are returning a value from the SP, you have to add a parameter (mapping direction: ReturnValue) to get the return value.
|||

Thanks for quick reply--opps I have fixed the SPROC see bold and set the result set in SSIS to none and still get the error--what I want to do is return the output from the Exec SQL task into a variable giBatchID Int32 and then connect to a script task and if value of giBatchID = 1 then fail the task and connect to an SMTP task to send an alert to the customer. The showstopper is the Execute SQL task with OLEDB connection to SQL server 2005 table--it simply does not work, there is no way that it will pick up a single row output from a mple OLEDB connection: Any ideas guys?

CREATE PROCEDURE CheckCatLog
@.OutSuccess INT OUTPUT
AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @.RowCountCAT INT
DECLARE @.RowCountLOG INT

these totals should match
SELECT @.RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT)
SELECT @.RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG)
--PRINT @.RowCountCAT
--PRINT @.RowCountLOG
BEGIN
IF @.RowCountCAT <> @.RowCountLOG
--PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file'
--RETURN 1
SET @.OutSuccess = 1

RETURN @.OutSuccess
END
GO

|||You can try Parameter Mapping, set direction to Output and map the parameter name 0 to the user variables.

Also set a default to @.OutSuccess to 0 instead of null
|||I do not understand why you would want to make OutSuccess an OUTPUT parameter and the return value.

My suggestion would be to make it just an OUTPUT paramter and not the return value. So, leave the second line in SP as it is, but comment out RETURN part. Set ResultSet=None in your task. Set SQLStatement="CheckCatLog ? OUTPUT". Then add an output parameter (variablename=giBatchID, Direction=Output, Type=Int32 and Parameter Name=0).