Tuesday, June 07, 2011

Strange error in SSIS for precedence constraint expression

I received a strange error while trying to run a SSIS package in Visual Studio 2008. The error reflected that I had set the precedence constraint incorrectly.


Following is the screenshot of the package that I had created



The parent 'Execute SQL Task' would run a query that would fetch a single integer field from a table in the the database.
select value from tmp_SSISbranch where name= 'condition2'
I had created a variable 'Convalue' in the package by choosing SSIS --> Variables from the menu bar of Visual Studio, for saving the field value.

I had added 3 different precedence constraints - 1 for the failure of the parent task, and 2 constraints for 2 different ranges of values for the integer field. On setting the expressions for the precedence contraints, there was no prompt or warning, but when I tried to execute the package, I was faced with the strange error.

Error at Package: The expression "@Convalue > 10" must evaluate to True or False. Change the expression to evaluate to a Boolean value.
Error at Package: There was an error in the precedence constraint between "Execute SQL Task" and "Data Flow Task".
 (Microsoft.DataTransformationServices.VsIntegration)

I had ensured that the precedence constraints evaluated to a boolean value, so the error puzzled me.


After toiling for hours and looking up for resources on the net went in vain, I realized that the issue must be because of the variable that I had created for saving the result of the query executed by the parent task.

I had created the package variable with the scope of the parent task rather than creating it as a package variable. Once I deleted and recreated the variable with the scope as 'package', the execution ran fine.

So, ensure that the variable is of the correct scope.




2 comments:

japs said...

Thank you so much for this info. I also spent hours deliberating what to do and searching online for a solution to the same problem. With your help, it was a peace of cake.

balaji said...

Thanks a lot japs..