Passing a variable to dtsx package results in unexpected precedence constraint evaluation

Question

I have a dtsx package with a precedence constraint that evaluates an expression and a constraint. The constraint is "success" and the expression is "@myVariable" == 3. myVariable is an int32, and when set in Visual Studio's design GUI the package executes fine. There are two other paths that check for the value to be 1 or 2.

However when I try to run the package from the command line and pass in a value for my variable, it errors out claiming the expression does not evaluate to a boolean!

Command: dtexec /F "c:myPackage.dtsx" /SET \Package.Variables[User::myVariable].Properties[Value];3

Error: The expression "@myVariable == 1" must evaluate to True or False. Change the expression to evaluate to a Boolean value.

The fact this runs fine from the GUI and that microsofts documentation claims == (intuiatively) returns a boolean has me very confused. I've also tried surrounding the 3 in double quotes in my command with no luck, and now I am out of ideas.

Anybody have an idea of what is going on?

Answer 1

Hmmm not sure is the best way but a workaround I found that seems to be working is for my expression to be:

(DT_I4)@[User::myVariable] == 3

Answer 2

Not sure if it causes the problem, but you are using a slightly odd syntax to set variable value, try

dtexec ... /SET \Package.Variables[User::myVariable].Value;3

Note I'm using .Value, instead of .Properties[Value]. .Value is the official way recommended by Books Online. Maybe the .Properties[Value] syntax also happens to work, but changes the variable type.

Answer 3

Sorry took me so long to get back to this thread! But (DT_I4)@[User::myVariable] == 3 did the trick. Thanks!

Answer 4

Always use the .Value property of the variable.

Answer 5

This seems to be a bug in the initial release of dtexec.exe. I have version 9.00.3042.00 wich is like, SQL Server 2005 SP2, installed in my dev enviro. We have a second test machine that is on version 9.00.1399.06 . The only place I see this failure is on the test box. My dev. box runs to completion. I added an explicit type conversion (DT_BOOL) to my boolean variable that was part of my expressions and the error went away.

Also the .Value comment above is incorrect. It is supposed to be .Properties[Value] , 'cause the Value of a variable is simply an item in the global variable properties collection. This isn't C# or VB.net syntax. This is SSIS syntax.

Answer 6

Seems like a bug in ssis because the value your passing in via the command line is not being automatically casted to the configuration variables' type. SSIS seems to treat values passed in via cmd line as strings, which causes issues at run time. Either change your variable to a string and do the comparison on that, or use the (DT_I4) to cast in the expression.

Answer 7

It's definitely a bug in dtexec - I had a similar problem (setting an integer value via the /set command on a dtexec command line) that complained further in the package execution the (output) variable was of the wrong type when returned from a stored procedure.

Omitting the set of that value (was setting it to zero anyway) fixed up the package execution error. Not sure what you'd do if you really needed the value - they seem to work fine as input parameters, just screws up when you want to use them as output parameters and they aren't strings.

Answer 8

I have the same problem if enable XML package Configuration. The /SET is ignored. Value into config XML have priority on command /SET.