January 7, 2009 at 5:30 am
Hi folks
I have a simple package with 2 Execute Sql tasks.
Task1 is there to get an output parameter value from a stored proc, and place it into a package variable.
If the value of this variable is an empty string, I don't want Task2 to be executed.
In the Precedence Constraint Editor, I have selected and EvalOp of "Expression", and an expression of @[User::MyVariable]!="", with Logical AND (not that the latter matters as I only have one constraint - I think. Doesn't seem to make any difference as far as I can tell anyway)
Now, while the value of the output parameter is indeed an empty string, Task2 still runs.
If I change the stored proc so that the output parameter gets a value of 'ABCD' instead of empty string when the relevant conditions apply, and change the expression in my precedence constraint to @[User::MyVariable] == "ABCD", Task2 doesn't run.
I am a bit baffled as to why @Variable != "" doesn't seem to be recognised - what am I missing?
Many thanks
Maria
January 7, 2009 at 5:43 am
hi maria,
i need a clarification.. in the result set if the procedure returns a null value means then it will show an error. you don need to use constraint itself..how first execute sql task will succeed if row set returns null value. check once wether u have set rowset property as single row and assign itto a variable
January 7, 2009 at 5:48 am
hi dyana,
Thanks for your response.
The output variable will either be an empty string, or will actually contain some text. This text will be used further down the line in the package, but only if it's not blank - hence why I don;t want Task2 to run if it's blank.
The procedure doesn't error when there's a null result set.
January 7, 2009 at 5:55 am
If you change the constraint to
@[User::MyVariable]==""
does it still run? That is, is the constraint being completely ignored?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 7, 2009 at 5:58 am
if it returns null value means the following error will occur in the execute sql task 1 and if you set precedence constsint as success means then the second execute task will not run.
here version is the user defined variable
An error occurred while assigning a value to variable "version": "Single Row result set is specified, but no rows were returned.".
follow this,
in general tab:
set result set to single row
in sql command writ
select column_name from table_name where condition
in result set tab
set result set name 0
vaiable name to your variable's name.
hope you understand this.
for this task shud succeed the query have to return a value. it is a must. no room for null values here
January 7, 2009 at 5:58 am
Phil,
Interesting, Task2 does not run when @Variable=="" is the expression (and the output param is indeed an empty string, so one would expect the task to run)
I guess this is something to do with the empty string side of things then?
January 7, 2009 at 6:05 am
dyana,
I'm sorry, but I'm not sure I understand what you mean.
Task1 will not fail - it either populates an output parameter with '' or with some other text.
Are you suggesting that I stop the execution of Task2 by "forcing" a failure? I would rather not do that.
Thanks again
Maria
January 7, 2009 at 6:12 am
no no i dint mention it.... what im saying is if you set result set to single row means then by default the query have to return a value. if it returns null value means the task will fail....in your case it is returning a value always i think.. try with model table.. create one model table with one column with no data in it... in the sql command select the value of that empty column to a variable.. then u can understand the concept
January 7, 2009 at 6:16 am
maria.protopapadaki (1/7/2009)
Phil,Interesting, Task2 does not run when @Variable=="" is the expression (and the output param is indeed an empty string, so one would expect the task to run)
I guess this is something to do with the empty string side of things then?
Sure looks that way. Maybe the "" somehow morphs into a NULL on the way through.
Maybe you could try something like Len(var) == 0, or
var & "" == ""
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 7, 2009 at 7:15 am
Okay, I think I've figured out why I was confused.
As I understood it, if the Expression evaluates to TRUE, the next task will run
If it evaluates to FALSE, the next Task will NOT run.
Looks like it's the other way round, ie the next task will be constrained (not run) if the expression is TRUE.
Thanks for your help, everyone
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply