SSIS - 派生列计算错误 [英] SSIS - Derived Column Calculation Error

问题描述

在 SSIS 派生列中的计算存在一些问题.

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)

我收到错误 -

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

将此计算粘贴到我的 C# Web 应用程序中导致了类似的问题 -

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

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

它在抱怨这个部分 - 4206 * 4206 * 4206.也可以通过更改它在 Web 应用程序中解决 4206 * 4206 * 4206L，但我不知道如何在 SSIS 包中解决这个问题.

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.

提前致谢.

编辑

溢出异常现已解决，但新旧计算返回的值略有不同，例如使用 415 作为输入值而不是 4206 -

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 -

旧计算值 = 419.014582新计算值 = 419.042100

Old calculated value = 419.014582 New calculated value = 419.042100

准确的计算是-

旧 - 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))

编辑

关于计算中的细微差异的第二个问题作为一个新问题打开.

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

推荐答案

为什么不直接改变

((4206 * 4206 * 4206) * 0.000000010000000)

到

(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)

这篇关于SSIS - 派生列计算错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！