I have a stored proc that has output variables containing an error message when a raiserror is raised. The stored proc also return a return code. The severity level of the RAISERROR is 16.
When this is executed in Mgmt Studio, the stored proc fails as expected, but the output variables and the return code still return.
When I use the execute sql task in SSIS, the task fails, but the output parameters and the return value never get populated.
I can do a try...catch in the execute sql, but this just gives me the ERROR_MESSAGE() result, but I lose the return value and the other output variables.
I don't mind the execute sql task failing once I get the return value and output parameters populated, but this is not the case. Does anyone know whether I can get the output parameters and return value to be returned when the execute task fails due to a RAISERROR event?