CODEWITHSUNDEEP
sql-serverssisssis-2012
Robert Smith
Robert Smith

Reputation: 31

Save SQL print text into SSIS variable

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

Upvotes: 1

Views: 558

Answers (1)

Amira Bedhiafi
Amira Bedhiafi

Reputation: 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 : enter image description here

Then in your SQL Task, in the SQL Statement :

EXEC  proc_testExistence ? OUTPUT

Create this mapping :

enter image description here

Modify the Precedence Constraint for the first condition Success :

enter image description here

And for the second condition Failure :

enter image description here

Your flow is something similar to this :

enter image description here

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

Upvotes: 1

Related Questions