I have a stored procedure that performs a bunch of table inserts and updates in a transaction. The basic structure of the stored proc is given below. I am executing this stored proc from an Execute SQL task in a SSIS package. I am puzzled why the package pauses on the Execute SQL step, keeping it yellow indefinitely. When I run SQL Server Profiler I can see that the stored proc has been called and completed. I copy the command from Profiler into SSMS and execute, which reveals an error - one of the table inserts failed due to a constraint violation. Clearly the CATCH block has been triggered and the transaction rolled back. Why does SSIS not know that the stored proc has failed? If I run sp_who2 this tells me that the SPID for the package connection is sleeping, awaiting a command. Do I need to modify the error handling of my stored proc or is there something I need to do in SSIS to ensure any error with the stored proc triggers a package failure?
CREATE PROC MyProc
…<some updates, inserts, deletes>
DECLARE @Error_Message VARCHAR(1000);
SELECT @Error_Message = 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10))
+ ', Error Message: ' + CAST(ERROR_MESSAGE() AS VARCHAR(800))
+ ', Stored Proc: ' + CAST(ERROR_PROCEDURE() AS VARCHAR(100));