How to tell the package to run task1 instead of task 2 based on a boolean true or false variable

Question

How to tell the package to run task1 instead of task 2 based on a boolean true or false variable

adam spencer

Hall of Fame

Points: 3926
More actions
March 9, 2011 at 10:01 am

#234956

Hi,
So I have an Execute SQL task that queries an sql table an returns 0 or 1 bit datatype to a single row result set in an SQL Task named "Check if Contract 5407 exists in the Data warehouse tables" an assigns the value to an SSIS user::blnIs5407exists
If it does exist, boolean = true, I want the next step to run task1(use legacy steps), if false I want the next step to run task2(data source is from Warehouse table) instead.
I must be going crazy, surely it is an easy answer, all logic needs conditions.
Thanks in advance

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SQLCHENNAI SSC Enthusiast Points: 126 More actions · Answer 1

You could use a function in the precedence control. Use an expression in the precedence control to check the value of the variable and proceed.

adam spencer Hall of Fame Points: 3926 More actions · Answer 2

Thanks you, You are right. Did not think to look inside the constraint editor and its properties.

I found an exellent tuturial to use expression within constrains here:

http://www.simple-talk.com/sql/ssis/working-with-precedence-constraints-in-sql-server-integration-services/

I made 2 constraints (arrows) from my sql tasks that holds the boolean variable pointing to 2 different tasks and put in the editor for each constraint:

@bln5407Exists=1 and in the other I am struggling with the SSIS expression for 'not equal to' for the second constraint because the expression in the 2 constraints need to be mutually exclusive(meaning in this case they cannot both evaluate to true). So I have to figure out how to say in the second one 'not equal to' becuase more than 1 constraint cannot have an expression evealuating to true.

I cannot put @bln5407Exists=1 in the constraint(arrow )editor going to task1 and @bln5407Exists=0 going to task 2.

Anyone know the SSIS equivalent to not equal to is. I tried <> and != already and it did not like it.

Adam

adam spencer Hall of Fame Points: 3926 More actions · Answer 3

SSIS Control flow in an SSIS package using expressions in constraint editors

My solution and it works great is below:

--below section used for testing

update tbljobcontroller

set ParameterValues = '20100501,H4527,H5407'

where JobName = 'CMS_Import_EDW'

select * from tbljobcontroller

where JobName = 'CMS_Import_EDW' and ParameterValues Like '%5407%'

--above section used for testing

--Boolean approach

create table #5407

(ReturnValue bit)

IF EXISTS(SELECT ParameterValues FROM tbljobcontroller WHERE JobName = 'CMS_Import_EDW' AND ParameterValues Like '%5407%')

BEGIN

insert #5407 (ReturnValue) values (1)

--print 'return value 5407 found '

END

ELSE

BEGIN

insert #5407 (ReturnValue) values (0)

--print 'return value NOT found 5407 '

END

select ReturnValue from #5407

drop table #5407

--This scalar result goes into an SSIS boolean variable user::@Bln_EDW5407 in the result set (set for single row) in an SQL Task

--////////////TO CONTROL FLOW I am using the PRECEDENCE CONSTRAINT editors pointing to legacy file approach and new EDW source for 5407, below are my settings: ///////////

--Evaluation options - Expression (makes it blue)

--value - success is greyed out becuase above not set to Contraint and expression

--expression - @Bln_EDW5407==true In the other precedence constraint it is @Bln_EDW5407==false

--Logical OR is selected for both although this will not matter since I have only 1 precedence executable (task) pointing to it.

--If I had more that one, Logical OR would mean atleast 1 precedence task pointing to it

-- would have to succeed before the constraint executable(task) will be run

Hopes this helps