3

My SQL Script is storing in @gempar = NULL and in @beneficiary = '2018-01-01'. In need to Execute the next Execute SQL Tasks based on the content of those variables. IF ISNULL(@gempar) = True then we don't run the following task (because then @gempar is equal to NULL). On the other hand, IF ISNULL(@beneficiary) = False then we run the following task (because @beneficiary is not NULL).

enter image description here

The next image is the Result Set from the Execute SQL Task:

enter image description here

I created the 2 variables beneficiary and gempar in SSIS but I am not sure if they should be written as following in the expression column: @[User::gempar] and @[User::beneficiary] or as they are now on the next image:

enter image description here

This is the SSIS Control Flow:

enter image description here

The Precedence Constraint Editor's Output should be True. Why is it not catching the value in @beneficiary ?

enter image description here

I expect the TRUNCATE GEMPAR TABLES task to stop and the TRUNCATE BENE TABLES task to continue.

Boels Maxence
  • 349
  • 3
  • 16
  • 1
    What is your question? I have no idea what you're trying to ask. What if `gempar` is true AND `beneficiary` is false? – Tab Alleman Jan 30 '19 at 15:54
  • I would like to Conditionally Execute TRUNCATE GEMPAR TABLES or/and TRUNCATE BENE TABLES based on the value in the 2 variables (@gempar and @beneficiary). The need is to execute the flow only if the variable is not NULL. – Boels Maxence Jan 30 '19 at 16:00
  • "I am not sure if they should be written as following in the expression column: @[User::gempar] and @[User::beneficiary] or as they are now on the next image: " Why don't you try it both ways and see which one works? – Tab Alleman Jan 30 '19 at 16:03
  • I did it but the two ways are giving me the same result. Which is both pipe lines are running. I need only the right one to execute because @beneficiary is not null. – Boels Maxence Jan 30 '19 at 16:08
  • Your expression syntax is wrong. It should just be: `LEN(@[User::beneficiary]) > 1`. – digital.aaron Jan 30 '19 at 16:41
  • When Evaluating : `LEN(@[User::beneficiary]) > 1`, I have False. It should be True. Is it mapping well `@[User::beneficiary]` from ssis and `@beneficiary` from ssms? – Boels Maxence Jan 30 '19 at 16:58
  • When I use Execute SQL tasks to assign a value to a variable, I always alias the column name(s) and then use the column name(s) as the Result Name to assign Result Sets to variables. What is the datatype of `dbo.PARAMS.[Start date]`? – digital.aaron Jan 30 '19 at 18:45
  • `[Start Date] datetime` When creating the variables, what should I write in the Value or Expression column? – Boels Maxence Jan 31 '19 at 08:57
  • I Solved this issue by using `@[User::beneficiary] != ""` because ssis reads NULL values as empty cells. – Boels Maxence Jan 31 '19 at 12:49

1 Answers1

2

SSIS doesn't allow for SSIS variables to be null. It's somewhat older and in a slightly different context, but this article further details SSIS variables in regards to null. Since the variables you're using are of the string data type, an option compare the variable to a blank string. Keep in mind, that this does not guarantee these variables were null as a result of what was performed in the Execute SQL Task. You may need to substitute blank strings or another value in your SELECT statement for this. For example, COALESCE(@beneficiary , '').

Right-click the Precedence Constraint between the tasks select Edit. Then change the Evaluation Operation to Expression and add an expression as follows. Depending on your expected outcome, the Evaluation Operation may need to be changed to Expression and Constraint, with the status of the prior task set in the Value field. The example below returns true when the @[User::beneficiary] variable is null, which will allow the following task to execute as you described. You can reverse this, i.e. use !=, for the @[User::gempar] variable.

@[User::beneficiary]) == ""
Hadi
  • 36,233
  • 13
  • 65
  • 124
userfl89
  • 4,610
  • 1
  • 9
  • 17