Mapping fails for the output of a stored procedure in SSIS

Question

I have the following stored procedure :

CREATE PROCEDURE proc_testExistence AS  
DECLARE @Result AS varchar(10)  
  
IF EXISTS (SELECT  [Category ID]  
      ,[Category]  
  FROM [PBIChallenge].[dbo].[Category] WHERE [Category ID]=55)   
SET @Result = 'Success'  
else  
SET @Result = 'Failure'  

I want to launch two packages depending on the value of @Result :

• If @Result="Failure" execute package 1
• If @Result = "Success" execure package2

I created a variable in my SSIS package vResult as String with value "Success"

This is my SQL Task configuration :

And in the parameter mapping :

I have set a Precedence Constraint like below :

If Success : Expression and constraint :

Success :  @[User::vResult] == "Success"  

If Failure : I change the condition to : @[User::vResult] == "Success"

When I execute my package I get this following error :

[Execute SQL Task] Error: Executing the query "EXEC proc_testExistence
@Result ?" failed with the following error: "Incorrect syntax near '@P1'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Answer 1

The stored procedure is not returning anything. You need to change SET to SELECT in both spots.

SELECT @Result = 'Success';
SELECT @Result = 'Failure';