SSIS Expression Task - Does it even work?
-
Okay, so I gave up on setting a string variable... and now I'm trying to just set a value for a Boolean variable, based on whether or not a variable with an integer row count is greater than zero or not. This continues to fail with the error:
Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.
I know that the variable being set is a Boolean data type, and I've checked the properties of the Expression Task to be sure that the variable I want set is the one set as the "ExecValueVariable" property. Does this feature even work ? Anyone? Anything ?
I was previously just trying to get precedence constraints to flow the execution one way with a zero row count, and another way with any value greater than zero. What in heck am I doing wrong?
- This topic was modified 4 years, 3 months ago by sgmunson. Reason: Added picture for clarification
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Well folks, this one sure was interesting.... it now appears that setting the ExecValueVariable to anything other than "<none>" causes it to fail, although it may be that setting it to the same variable as the one you''re trying to update is the cause. Just seemed like that was the way it was supposed to be, but apparently not. However...
As much as I'm happy to get past that particular sticking point, I'm now stuck yet again at exactly the same place that I was before... I get as far as the Expression Task, having fed in a file with nothing but a header row and no data rows. As the package gets past this step when a file with data arrives, clearly the precedence constraint that tests for a True value of the variable being set, is working. But then why in heck would a precedence constraint that checks for a False value, fail to allow further tasks to run ??? I continue to scratch my head...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
okay folks, here was the entire issue:
1.) I had misinterpreted some of what I saw online, and ended up setting the property "ExecValueVariable" to the variable I had wanted to set the value of. This is not how that is supposed to work.
2.) I had only used the expression that I wanted to set the value of the variable to, not realizing that what is actually needed is as follows:
@[User::varIWantToSetTheValueOf] = SSIS Expression ...
That solved that problem for me, but as it turned out, it wasn't actually something I really needed to do. I then discovered that you can use values in an EXEC statement that are intermixed with your ?'s for parameter values... you just have to bypass the prepare and not try to parse the query. It all works fine.
However, even with that realization, all of my troubles were NOT over. I still couldn't seem to get the precedence constraints to work. As I had two such constraints (albeit coming from two different tasks) that both went to the same destination, they both needed to have an OR chosen instead of AND, as those conditions were 1) mutually exclusive, and 2) while unrelated in the tasks they came from, the AND vs OR thing applies to all constraints "PRECEDING" a given task, and despite more than 5 years of using SSIS, this is one I had never run into before. Fortunately, I finally have everything working. Yee ha !!!
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Thanks for following up with the solution.
-
Glad it was helpful, homebrew... It was a real bear of a problem, and very difficult to find the information online that was needed to quickly solve it. Only rarely does anyone explain the "dirty details" that if you get wrong, totally screws stuff up.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply