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

Just change your PRINT to SELECT 'EXIST' AS Status and SELECT 'NOT_EXIST' AS Status set the result set on the execute sql task to single row then on the Result Set page map it your variable. Result Name would be Status and then pick your variable. — Tim Mylott
– Tim Mylott, Commented Jan 21, 2021 at 22:00
Thanks @TimMylott !! :) and can i execute one flow or another depending on the content of the variable?? — Robert Smith
– Robert Smith, Commented Jan 21, 2021 at 22:55
@TimMylott Subqueries are not allowed in that context, you can't use PRINT with SELECT — user9517769
– user9517769, Commented Jan 22, 2021 at 10:57
@SandraGuilepZouaouiZandeh you're correct, I wasn't saying use print with select. Your answer is an option as well using an OUTPUT, both would have accomplished the same thing. — Tim Mylott
– Tim Mylott, Commented Jan 22, 2021 at 14:41

user9517769 · Accepted Answer · 2021-01-22 14:11:08Z

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.

answered Jan 22, 2021 at 14:11

user9517769

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Save SQL print text into SSIS variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related