DECIMAL with Scale 2 truncation of SSIS Differs from Transact SQL

Hi,
I have a derived column transformation which adds a new column of type Decimal with scale 2.

The expression is (6800 / 464)

Runs fine but it returns

14.65

While if I run any of the following queries in query analyzer

SELECT 6800 /

464

OR

SELECT CONVERT(DECIMAL(10,2), 6800 / 464)

They give me 14.66.

My question is, why both the tool of same product differs in the way they work ? and How could I have SSIS to work like TSQL ? I tried typecasting in derived column expression and ROUND function too. But still, the output is same.

Any help in this will sincerely be appreciated.

Thanks

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

|||

DarrenSQLIS wrote:

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

But the intuitive way doesn't work: (both report 14.65)

(DT_NUMERIC,6,2)((DT_NUMERIC,6,2)6800 / (DT_NUMERIC,6,2)464)
(DT_NUMERIC,6,2)6800 / (DT_NUMERIC,6,2)464|||I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.|||

DarrenSQLIS wrote:

I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

The only reason I say that casting the inputs is because in this example, they would be considered integers, not numerics.

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

|||

Phil Brammer wrote:

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

It is important to note that the SSIS expression language follows C-style rules, not T-SQL. Behaviour in this case is a little different.

Phil Brammer wrote:

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

In the case of the SSIS expression language, there should indeed be no difference between

(DT_NUMERIC,4,2)3948/38

and

(DT_NUMERIC,4,2)3948/(DT_NUMERIC,4,2)38

since for binary operators, the operands will be implicitly cast to an appropriate result type before the operation is performed.

Thanks
Mark

|||Thankyou so much for your effort guys.

The problem was, SSIS Derived Column Trasnformation was truncating while TSQL was rounding.

So what I did is, I would force SSIS to compute higher precision value and then ROUND it to the precision I want.

so

ROUND( ((DT_DECIMAL,4)6800) / ((DT_NUMERIC, 4) 464) , 2)

and it returned me the 14.66 which matched the result of TSQL.

Thanks once again for your inputs which helped me