How to stop Rounding Values in SQL Server Integration Services?

Question

I'm using the SSIS package, there is nothing being changed from source to destination. However, the column which I'm paying attention right now has a slightly different datatype (which I hope doesn't matter) in the source and destination; the source has numeric (15,2) and the destination has numeric (16,2) and the data in the source is 4.75 and it changes to 5.

The 1 digit reduction in the decimal places, which I'm sure, should not matter because I tested with the case.

Why is the value rounded?

Answer 1

There is obviously some important piece of information you are leaving out.

Have you looked at the data in the flow in a dataviewer to see where the change occurs?

Answer 2

Check the advanced editors of every task in the data flow (right click the task-->Show Advanced Editor). Go to the columns tab, and check the External, Input, and Output columns to make sure your column is never an integer.

My guess is you're pulling it as an integer (on the source), not as a numeric.

Answer 3

Agreed. There's something missing. Any chance you're doing some sort of operation (e.g. multiplication) by a constant integer somewhere (multiplying by 5 instead of 5.0) ?

Answer 4

I also had the same problem. There seems to be a bug in BIDS. My OLE DB task was previously set to have a price as an int. Realising my mistake I changed it to money but BIDS/SSIS kept rounding it and treating it as an INT. I tried everything and in the end the only thing to solve this was to delete the OLE DB task and drag a new one on. I used the same query, but with money and it worked fine. It's like BIDS/SSIS has a strange 'memory' and the only way round it is to start again!