SSIS Execute SQL Task and RaisError

If you want to use sp to call an exe program, many ppl reported the raiserror function cannot manually fail a SSIS package step. The problem is we should set the raiserror function parameter severity to be 11 above (11-16). (RAISERROR with an error code of 11 (which is a level SSIS should recognize) ), here is the link I found: http://ulrichandhelvas.wordpress.com/2007/04/04/ssis-ole-db-connection-managers-no-errors-allowed/.

The situation is if you have an exe program, and you want to write a sp to call the exe, when include the sp in your SSIS, if the exe exit code is not 0, the SSIS step will also fail.

I wrote an sp:

CREATE PROCEDURE usp_MyProc

AS
BEGIN
SET NOCOUNT ON;

DECLARE @SQL AS VARCHAR(800)
DECLARE @ret INT

BEGIN
SET @SQL = "C:\Mytest\test.exe"
EXEC @ret = myserver.master.dbo.xp_cmdshell @SQL
IF( @ret <> 0)
BEGIN
RAISERROR ('An error occured',11,1)
END
ELSE
BEGIN
Print 'good'
END

END

END


In this case if my test.exe exit 1, my SSIS step will aslo fail.