Resolve the divide by -zero error occurred in SSIS Expression

Question

Resolve the divide by -zero error occurred in SSIS Expression

Saintmount.Sql

Ten Centuries

Points: 1023
More actions
June 20, 2011 at 10:43 am

#253169

Hi,
How do resolve the divide by -zero error occurred in SSIS Expression.
I need to write below Expression.
I'm using the 3 int32 variable in SSIS.
1. tblA ->which hold the count 1st tbl
2. tblB->which hold the count 2nd tbl
3.tblC->need to apply below expression
Row_count(tblA) /(Row_count(tblA) -Rowcount(tblB)) *100
I've below expression for the tblC variable Evaluated expression .
((@[User::Row_Count_tblA]) / (@[User::Row_Count_tblA] - @[User::Row_Count_tblB])) * 100
But i 'm getting the divide by -zero error occurred in SSIS Expression.
can you help me out how do form the above mention expression.
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

See if something along these lines will work:

@Row_Count_tblA /
(
(@Row_Count_tblA - @Row_Count_tblB) == 0 ? NULL(DT_I4) : (@Row_Count_tblA - @Row_Count_tblB)
)
* 100

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

That's a really odd percentage result calculation. What are you trying to get at in English? The math seems kinda wonky to me.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Saintmount.Sql Ten Centuries Points: 1023 More actions · Answer 3

Thanks For your Reply.

There is some change in my Expression.

It is like (Row_count(tblA) -Rowcount(tblB)) / Row_count(tblA) *100

I have changed the your Expression based on my modification. As like below… The Expression evaluated without any error.

((@[User::RowCount_tblA] - @[User::RowCount_tblB) == 0 ? NULL(DT_I2) : ( @[User::RowCount_tblA] - @[User::RowCount_tblB])

/

((@[User::RowCount_tblA)== 0 ? NULL(DT_I2) : ( @[User::RowCount_tblA]))) *100

I am getting the result of above expression as 0. But I should get some value based on 2 variable values.

So this is the Issue now ….

Let me explain the my variable property.

1.RowCount_tblA is int32 ,value as 0 (which hold the count 1st tbl)

2.RowCount_tblB is int32 ,value as 0 (which hold the count 2nd tbl)

3.RowCount_tblC is int32 ,value as 0 ( I have written the Evaluated expression for this Variable)

Since my variable[RowCount_tblC] getting the value as 0,while Evaluating the above expression.

So at last I’m getting 0 value.

But actually I should get like

RowCount_tblA ->713

RowCount_tblB ->625

RowCount_tblC -> calculate from Expression

((713-625)/ (713)) * 100 =12.34 is expected value.

But I am getting the expression value as 0.

Note :

Even I set the data type as String for the RowCount_tblC. After execution of package its changed to Int32.

Please help me out how to find out correct solution.

Thanks.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Oops, I neglected to mention one thing...I changed the variables from Int32 to Double. Please do that and you'll hopefully see what I see.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Saintmount.Sql Ten Centuries Points: 1023 More actions · Answer 5

Thanks For your reply…

But I am facing issue while changing the data type from INt32 to double.

Let me explain some more detail my task..

I have 2 data flow for selecting the 2 row count value.

1st DF-> : Source tblA with Row count transform, I have selected the VarA as variable, which is double data type. Row count transform not accepting the double data type variable.

I’m getting message as in the row count transform itself..

Error at data flow task :the variable “user::A” specified by variable name property is not an integer. change the variable to be type VT_I4,VT_UI4,VT_I8 OR VT_UI8.

Same issue for the 2ND Dataflow also.

I have tried another approach to get row count its like…

I have created 2 SQL execute task to select count from the TablA & TablB. When I assign the result set as the Double data type variable A & B respective sql execute task…

While executing the SQL execute task getting below message…

[Execute SQL Task] Error: An error occurred while assigning a value to variable "A": "The type of the value being assigned to variable "User::A" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.".

So changing data type from Int32 to double throws error.

Any idea….

Thanks

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

SSIS is type-safe and is fussy about implicit conversions from one data type to another which is what you;re experiencing. Basically, in all the cases you mentioned, it's complaining about you trying to stuff a value of type Int32 into a variable of type Double/Decimal.

Try this:

> Add a user variable named Row_Count_tblA_dbl

> Set the Data Type to Double

> Set it up as an expression and make the expression: (DT_DECIMAL,2)@Row_Count_tblA

> change Row_Count_tblA back to Int32

Now do the same steps to make a new variable named Row_Count_tblB_dbl and pull its value from Row_Count_tblB.

Once you do that you should be able to change Row_Count_tblC to type Double as well and use this expression to define its value:

(DT_DECIMAL,2) (@Row_Count_tblA_dbl /
(
(@Row_Count_tblA_dbl - @Row_Count_tblB_dbl) == 0 ? NULL(DT_I4) : (@Row_Count_tblA_dbl - @Row_Count_tblB_dbl)
)
* 100)

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato