CODEWITHSUNDEEP
sql-serverssis
user1948635
user1948635

Reputation: 1409

SSIS - Derived Column Calculation Error

having a few issues with a calculation in a derived column in SSIS.

I am trying to perform the following calculation -

4206 + ((4206 * 4206) * 0.000150000000000) + ((4206 * 4206 * 4206) * 0.000000010000000)

I am getting an error of -

**The magnitude of the result of a binary operation overflows the maximum size for result data type**

Pasting this calculation into my C# Web application caused a similar issue -

**The operation overflows at compile time in checked mode**

It was complaining about this section - 4206 * 4206 * 4206. It can be resolved in the web application by changing it too 4206 * 4206 * 4206L, but I have no idea how to resolve this in the SSIS package.

Has anyone come across this issue before? Any ideas would be appreciated.

Thanks in advance.

EDIT

Overflow exception now resolved, however the old and new calculations are returning slightly different values, e.g. using 415 as the input value instead of 4206 -

Old calculated value = 419.014582 New calculated value = 419.042100

The exact calculation is -

Old - InputValue + ((InputValue * InputValue) * (DT_DECIMAL,20)@[User::SquaredValue]) + ((InputValue * InputValue * InputValue) * (DT_DECIMAL,20)@[User::CubicValue])

New - InputValue + (((DT_DECIMAL,20)@[User::SquaredValue] * InputValue * InputValue)) + (((DT_DECIMAL,20)@[User::CubicValue] * InputValue * InputValue * InputValue))

EDIT

2nd issue around slight differences in the calculation opened as a new question.

Upvotes: 1

Views: 788

Answers (1)

juergen d
juergen d

Reputation: 204746

Why not just change

((4206 * 4206 * 4206) * 0.000000010000000)

to

(0.000000010000000 * 4206 * 4206 * 4206)

to avoid such big numbers in the internal row calculation. I'd use

4206 + (0.00015 * 4206 * 4206) + (0.00000001 * 4206 * 4206 * 4206)

Upvotes: 5

Related Questions