question

Henrik Staun Poulsen avatar image
Henrik Staun Poulsen asked

SSIS Derived Column: Round or Floor

I ran into something perculiar in SSIS today.
In a Derived Column package I was trying to truncate a numeric value to an integer, like this (DT_I4)[MyNumericValue] It turns out that this does not do a truncate, but it rounds (up or down) to the nearest integer.
So I had to do this to get it to truncate: (DT_I4)FLOOR([MyNumericValue]) I tried searching for an answer, but was not able to find any.
Now there is an explaination to be found.
ssistruncateroundmathematics
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
Only to be completely correct to really truncate (cut off decimal places) from signed numeric value you have to write following expression: SIGN([MyNumericValue]) * FLOOR(ABS([MyNumericValue])) As single floor function returns the largest integer value, that is less then or equal to the value provided. In case of negative value eg. `FLOOR(-1.34)` will return `-2`.
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Henrik Staun Poulsen avatar image Henrik Staun Poulsen commented ·
Very important point, not to be missed. (This time I only had positive numbers, so I do not have to change anything in my code). But I think it is really naughty of SSIS to do a round. I still read the code as a Truncate, but it is really doing a Cast(round(x,0) as integer). To my immense surprise
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.