none
Constraint Expression Divide-by-zero error RRS feed

  • Question

  • I am trying to put a precedence constraint based on the percent of error rows in a data flow task.  Two variables, @ErrorRowCount and @ExtractRowCount, are assigned values in the dataflow.  When I try to enter the expression @ExtractRowCount==0 ? False : @ErrorRowCount / @ExtractRowCount < .05  I get the error A divide-by-zero error occured.  The right side operand evaluates to zero in the expression "@ErrorRowCount / @ExtractRowCount". 

    I can work around the problem by assigning a non-zero value to the ExtractRowCount at design time, and it appears the expression evaluates as expected when @ExtractRowCount is 0 at runtime.  Is there a way to accomplish this in the SSIS expression language without assigning an arbitrary value to my variable?

    Wednesday, March 17, 2010 6:54 PM

Answers

  • I don't think it can be achieved this way. SSIS is trying to evaluate the else part also but as the value for @ExtractRowCount is set to 0 it fails validation.
    Sudeep's Blog
    • Marked as answer by PrestoMN Wednesday, March 17, 2010 9:04 PM
    Wednesday, March 17, 2010 8:48 PM

All replies

  • Your expression is not working like you expect.  This is how SSIS is evaluating the expression you wrote:

    ((((@ExtractRowCount == 0) ? False : @ErrorRowCount) / @ExtractRowCount) < .05)

    Your expression is always dividing by @ExtractRowCount - which I know you don't want!  Surround your "false" clause with parentheses.
    Todd McDermid's Blog
    Wednesday, March 17, 2010 7:38 PM
    Moderator
  • I may have omitted the quotes in my original question, but I just tried each of the following:

    @ExtractRowCount==0 ? FALSE : (@ErrorRowCount / @ExtractRowCount < .05)

    @ExtractRowCount==0 ? FALSE : ((@ErrorRowCount / @ExtractRowCount) < .05)

    (@ExtractRowCount==0) ? FALSE : (@ErrorRowCount / @ExtractRowCount < .05)

    (@ExtractRowCount==0) ? FALSE : ((@ErrorRowCount / @ExtractRowCount) < .05)

    ((@ExtractRowCount==0) ? FALSE : ((@ErrorRowCount / @ExtractRowCount) < .05))

    All generate the identical error message:  A divide-by-zero error occured.  The right side operand evaluates to zero in the expression "@ErrorRowCount / @ExtractRowCount". 

    Wednesday, March 17, 2010 7:56 PM
  • I don't think it can be achieved this way. SSIS is trying to evaluate the else part also but as the value for @ExtractRowCount is set to 0 it fails validation.
    Sudeep's Blog
    • Marked as answer by PrestoMN Wednesday, March 17, 2010 9:04 PM
    Wednesday, March 17, 2010 8:48 PM
  • I think I'll just put a script task in the control flow to populate a branching variable.  Thanks for the help.

    Wednesday, March 17, 2010 9:04 PM
  • You shouldn't need to do that.  I'm surprised the precedence condition's expression does that - but if it does, it does.  You can add an SSIS boolean variable and place that expression inside it, then use that variable in your precedence constraint condition.  I'm certain I've done expressions with protection for division by zero and it functioned...
    Todd McDermid's Blog
    Wednesday, March 17, 2010 10:11 PM
    Moderator