SSIS (Execute SQL task) not recognizing return code from called Stored Procedure.

Question

SSIS (Execute SQL task) not recognizing return code from called Stored Procedure.

gdostal

Old Hand

Points: 343
More actions
October 1, 2010 at 11:41 am

#228936

Hello, first post here & I’ve not been able to find a similar post.
I have an SSIS Package w/several tasks – one of which is a (Execute SQL Task). This task calls a stored procedure & upon return must recognize any error conditions being set in the called csp.
Note that the called csp(1) can detect errors of its own but also calls "other" stored procedures that return their own error codes to the main csp(1).
Codes for detected error conditions are passed via the (RETURN @rc) command.
My problem is that when csp(1) is called and it then calls another csp(2) which detects an error condition – the SSIS task will NOT recognize the error code upon returning from csp(1). Conversely if csp(1) detects an error condition before calling another csp – the (Execute SQL Task) WILL recognize the error code.
Additional background:
We are using SQL Server 2008.
Here are snapshots of sections of csp(1) showing two different areas where error conditions can be detected.
--
--ERROR CONDITION (1)
--
IF @business_application NOT IN ('Foundation', 'Focus')
BEGIN
SET @Msg = 'Bum business_application.'
+ ' Business_application:' + @business_application
EXEC csp_LogMsg @Msg,0,L
SET @rc = 101
GOTO Pgm_Clean_Up
END
--
--ERROR CONDITION (2)
--
declare cursProduct cursor for
SELECT p.col1,2,3,4,5
FROM Product as p
FOR READ ONLY
OPEN cursProduct
FETCH cursProduct INTO @col1,2,3,4,5
WHILE @@fetch_status = 0
BEGIN
BEGIN TRY
EXEC @rc = csp_Ins_Upd_Party @Parm1,2,3,3,4
END TRY
BEGIN CATCH
SELECT
@ERROR_NUMBER= ERROR_NUMBER()
,@ERROR_MESSAGE = ERROR_MESSAGE()
,@ERROR_PROCEDURE = ERROR_PROCEDURE()
,@ERROR_LINE = ERROR_LINE()
IF @ERROR_NUMBER <> '0'
BEGIN
SET @rc = @ERROR_NUMBER
SET @Msg = 'Stored Procedure-' + @ERROR_PROCEDURE
+ 'failed. ERROR_LINE=' + @ERROR_LINE
+ ', SQL ERROR=' + @ERROR_NUMBER + '-' + @ERROR_MESSAGE
EXEC csp_LogMsg @Msg,0,L
BREAK
END
END CATCH
IF @rc <> 0
BEGIN
SET @Msg = 'Import Process aborted. Return Code:' + ltrim(str(@RC))
EXEC csp_LogMsg @Msg,0,L
BREAK
END
FETCH cursProduct INTO @col1,2,3,4,5
END-- end of While loop for cursProduct.
close cursProduct
deallocate cursProduct
Pgm_Clean_Up:
SET @Msg = 'Finished. Return Code:' + ltrim(str(@RC))
EXEC csp_LogMsg @Msg,0,L
RETURN @rc
<><><><><><><><>
Here is the SQL from the SSIS (Execute SQL Task).
<><><><><><><><>
Declare @rc int
EXEC @rc csp1 'Foundation'
IF @rc <> 0
BEGIN
RAISERROR('ImportTaskError',16,1)
END;
<><><><><><><><>
To recap:
When error condition(1) is detected in csp(1) the SSIS task will recognize the error code in @rc.
When error condition (2) is detected the SSIS task will NOT recognize the error code. In this case it appears the SSIS task will not execute any SQL past the (EXEC @rc csp1 'Foundation') command.
Has anyone encountered this? Any insight is appreciated.
Thanks, G.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

gdostal Old Hand Points: 343 More actions · Answer 1

Upon further research I have determine that under error condtion 2 - the SSIS task does receive the error code in the @rc variable however it now appears the RAISERROR command is not executing.

I am continueing to research why.

G.

gdostal Old Hand Points: 343 More actions · Answer 2

Update....

I found a link - (http://www.sqldev.org/sql-server-integration-services/raiserror-does-not-cause-sql-task-to-fail--why-5333.shtml)

suggesting to change the SSIS connection mgr to ADO.net.

I changed from OLE DB & error conditions detected in csp's are now correctly setting the "task failed" condition in the SSIS task via the RAISERROR command.

G.