Create an Error in an SSIS Package based on Return Value

Question

If using SSIS to run a stored procedure in an EXECUTE SQL TASK and return a value. If the procedure returns any value besides 0, I want the DTSX package (and the job running it) to error out. How do I create an error based on this return vale?

Answer 1

You don't say whether you're using the SP for any other purpose, or simply using it to check the return value.

The easiest way to do this is to check the return value inside the Execute SQL Task, and have it raise a SQL error (which will be trapped by SSIS). Replace your existing SP code with the following:

DECLARE @return INT

EXEC @return = ~your SP name~

if @return != 0
        RAISERROR('relevant error message',16,1)

This assumes that the SP has no other purpose in the SSIS package than to return this value.