SSIS (SQL Server 2005) Does not trap SQL exception

Question

I run an "Execute SQL Task" in SSIS It runs a stored procedure which does some validation In the Stored Procedure I have a RAISERROR command when something goes wrong. However when I test for this, this task fails to abort. I have Googled about this and found lots of references, but no solution that works for me. I have upgraded my SQL Server 2005 to service pack 3, but this does not make any difference. One reference suggests putting in PRINT statements when an exception is thrown, This does not work. So how do I fix this? The code in the stored procedure is;

ALTER PROCEDURE [dbo].[usp_VAL_Journey] 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @Month AS INT
                    , @Year AS INT
    SELECT TOP 1 @Year = DATEPART(YEAR, Date), @Month = DATEPART(MONTH, Date)
            FROM dbo.JourneyLandingTable

    SELECT TOP 1 *
            FROM dbo.JourneyMasterTable
            WHERE DATEPART(YEAR, Date) = @Year 
            AND DATEPART(MONTH, Date) = @Month
    IF @@ROWCOUNT > 0
    BEGIN
            RAISERROR('JourneyMasterTable already contains data for this month.', 16, 1)
            RETURN
    END

    SELECT DATEPART(YEAR, Date) AS year1, DATEPART(MONTH, Date) AS month1
    FROM dbo.JourneyLandingTable
    GROUP BY DATEPART(YEAR, Date), DATEPART(MONTH, Date)

    IF @@ROWCOUNT > 1
    BEGIN
            RAISERROR('JourneyLandingTable contains data for more than 1 month.', 16, 1)
    END
END

Answer 1

I have a similar thing but works fine for me. Not sure why it doesnt. The setup I have is I dont raiseerror at various places. I keep incrementing the error count and finally raise it as follows. It works perfectly - it aborts execution and all that good stuff.

declare @errorString varchar(100)
IF @rc > 0
BEGIN
    set @errorString = 'Error(s) occured when trying to run sp_blahblah error count ' + cast(@rc as varchar(10))
    raiserror(@errorString , 16, 1)
END

Answer 2

you might want to try returning some value "RETURN n"

ALTER PROCEDURE [dbo].[usp_VAL_Journey] 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @Month AS INT
                    , @Year AS INT
    SELECT TOP 1 @Year = DATEPART(YEAR, Date), @Month = DATEPART(MONTH, Date)
            FROM dbo.JourneyLandingTable

    SELECT TOP 1 *
            FROM dbo.JourneyMasterTable
            WHERE DATEPART(YEAR, Date) = @Year 
            AND DATEPART(MONTH, Date) = @Month
    IF @@ROWCOUNT > 0
    BEGIN
            RAISERROR('JourneyMasterTable already contains data for this month.', 16, 1)
            RETURN 10 --<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    END

    SELECT DATEPART(YEAR, Date) AS year1, DATEPART(MONTH, Date) AS month1
    FROM dbo.JourneyLandingTable
    GROUP BY DATEPART(YEAR, Date), DATEPART(MONTH, Date)

    IF @@ROWCOUNT > 1
    BEGIN
            RAISERROR('JourneyLandingTable contains data for more than 1 month.', 16, 1)
            RETURN 20 --<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    END
END

you should be able to tell if that code block was hit by checking the procedure's return value.