How to fix DTSX priority restriction estimation error when passing variable on command line?

Question

I have a dtsx package with a priority constraint that evaluates an expression and a constraint. The limitation is “success”, and the expression “@myVariable” == 3. myVariable is int32, and when it is installed in the Visual Studio GUI, the package runs fine. There are two other ways that check a value of 1 or 2.

However, when I try to run the package from the command line and pass the value for my variable, it mistakenly claims that the expression does not evaluate the 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 that this works fine with the GUI and that Microsoft documentation requests == (intuitively) return a boolean value is very confusing to me. I also tried to surround 3 in double quotes on my team with no luck, and now I have no ideas.

Anyone have any idea what is going on?

Answer 1

Sorry, so long that I returned to this topic! But (DT_I4)@[User::myVariable] == 3 did the trick. Thanks!

Answer 2

This is definitely a bug in dtexec - I had a similar problem (setting an integer value using the / set command on the dtexec command line), which later complained about package execution. (output) the variable was of the wrong type when returning from the stored procedure.

Omitting the set of this value (in any case, set it to zero), the error in executing the package has been fixed. I'm not sure what you would do if you really needed that kind of value - they seem to work fine as input parameters, just screw in when you want to use them as output parameters, and they are not strings.

Answer 3

Not sure if this is causing the problem, but you are using slightly odd syntax to set the variable value, try

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

Note. I use .Value instead of .Properties[Value] . .Value is the official way recommended by Books Online. The .Properties[Value] syntax .Properties[Value] also work, but it changes the type of the variable.

Answer 4

This seems to be a bug in the initial release of dtexec.exe. I have a version 9.00.3042.00 similar to SQL Server 2005 Service Pack 2 installed in my enviro. We have a second test machine, which is located on version 9.00.1399.06. The only place I see this failure is the test box. My maid box ends. I added an explicit type conversion (DT_BOOL) to my boolean that was part of my expressions and the error went away.

Also .Value comment is incorrect. This is assumed to be .Properties [Value], because the value of a variable is just an element in the collection of properties of global variables. This is not C # or VB.net syntax. This is the syntax of SSIS.

Answer 5

It seems like an error in ssis, because the value that you pass using the command line is not automatically added to the type of configuration variables. SSIS seems to treat the values ​​passed through the cmd string as strings, which causes runtime problems. Either change the variable to a string and compare it, or use (DT_I4) for the expression in the expression.