Conditional SSIS - execute one task or another based on the result

Question

I have the following tasks in SSIS:

In Check Stock task, I execute a stored procedure which returns 0 or 1:

    CREATE PROCEDURE [dbo].[SP_CheckStockAvailability]
AS

BEGIN

DECLARE @ItemGID nvarchar(250)=(SELECT TOP (1) ItemGID FROM XMLOrderlines WHERE Comparison='0')

SELECT CASE 
WHEN @ItemGID IS NOT NULL
THEN CAST (0 AS bit)
ELSE CAST (1 AS bit)
END AS Comparison
FROM XMLOrderlines 

END
GO

I would like to execute Reject Order (on the right) task if the result is 1 and, if not, to execute the one from the left. I set to export the result of the procedure in a variable, Boolean data type with a default value of "False".

If I edit the precedence constraint and I set Expression as evaluation operation and then choose the variable from the previous task, either way it does not go to the next task that is supposed to. What am I missing? I tried what I found on the web but nothing helped me. Thanks!

Answer 1

Solution

You have to set the following values:

Reject Order

Evaluation operation:   Expression and Constraint
Value:                  Success
Expression:             @[User::Result] = 1
                     OR 
                        @[User::Result]

Accept Order

Evaluation operation:   Expression and Constraint
Value:                  Success
Expression:             @[User::Result] = 0 
                     OR 
                        !@[User::Result]

Screenshot

Suggestions

I think it is better to add a TRY...CATCH block to your procedure, so if it encounters an error the result will be 1 and the Order is rejected:

CREATE PROCEDURE [dbo].[SP_CheckStockAvailability]
AS

    BEGIN

    BEGIN TRY  

        DECLARE @ItemGID nvarchar(250)=(SELECT TOP (1) ItemGID FROM XMLOrderlines WHERE Comparison='0')

        SELECT CASE 
        WHEN @ItemGID IS NOT NULL
        THEN CAST (0 AS bit)
        ELSE CAST (1 AS bit)
        END AS Comparison
        FROM XMLOrderlines 

    END TRY 
    BEGIN CATCH

        SELECT 1 AS Comparison

    END CATCH

    END
GO