Mapping fails for the output of a stored procedure in SSIS

Sandra Guilep 21 Reputation points
2021-01-22T12:13:28.95+00:00

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 :

59520-image.png

And in the parameter mapping :
59568-image.png

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.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,470 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 25,206 Reputation points
    2021-01-22T13:20:55.693+00:00

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

    SELECT @Result = 'Success';
    SELECT @Result = 'Failure';
    
    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful