sql server - Save SQL print text into SSIS variable

Question

I want to know if is possible save a PRINT SQL text into SSIS variable.

My query is:

IF EXISTS (SELECT * FROM TABLE WHERE month = '1' or month = '2' or month = '3' or month = '4' 
            or month = '5' or month = '6' or month = '7' or month = '8' or month = '9')
BEGIN
PRINT = 'EXIST' 
END
ELSE
BEGIN
PRINT = 'NOT_EXIST'  
END

I need exist or not_exist to execute a flowfile or another. I tried with ResultSet(SingleRow) but I am not able. What its the best way?

Thanks

question from:https://stackoverflow.com/questions/65835008/save-sql-print-text-into-ssis-variable

Answer 1

You can create a stored with your query like below :

ALTER PROCEDURE [dbo].[proc_testExistence]  (
@Result  varchar(10) OUTPUT ) AS

BEGIN

IF EXISTS (IF EXISTS (SELECT * FROM TABLE WHERE IN ('1','2','3','4','5','6','7','8','9'))
     SELECT @Result = 'Success';
ELSE
     SELECT @Result = 'Failure';
END

Then create a variable named vResult in your SSIS package :

Then in your SQL Task, in the SQL Statement :

EXEC  proc_testExistence ? OUTPUT

Create this mapping :

Modify the Precedence Constraint for the first condition Success :

And for the second condition Failure :

Your flow is something similar to this :

You can add a breakpoint as above to check the value of your variable during debugging.