SQL Server real/float precision changing during SSIS import to MS-Access database

Question

I am using SSIS to grab the results from 2 Views in a SQL Server database, union them (with appropriate mapping), filter them and place them into an Access database.

This process is working, but the precisions of certain data are changing.

I was already aware of the normal float/real problem of storing approximate values (e.g. http://stackoverflow.com/questions/1203069/sql-server-makes-up-extra-precision-for-floats), however the views being referenced by SSIS ("data transfer views") are selecting from other views ("part level views", which convert from nvarchars to real/float) which explicitly convert in the following way:

,CONVERT(real,ISNULL(FieldName, 0)) AS Alias

and appear in the data transfer view results to 2 d.p. The Access column is defined as a Number with Field size=Double, Decimal places=2).

Yet, the float/real approximated value is being displayed in access, rather than the figure to 2 d.p.

The access fields must be Number and not Text, so I can't recast to nvarchar.

Is there a simple solution to this?

Update: Changing the field size from Double to Single fixes this issue. Why does the Field size of Double change the precision of the number and why does it not display it according to the specified number of decimal places for a Double?

Answer 1

Interestingly enough, changing the Field size from Double to Single changes the way the number is displayed.

The storage range of a Single is fine, so the immediate problem is solved.

Why does the Field size of Double change the precision of the number and why does it not display it according to the specified number of decimal places?

Answer 2

Check the Access Help topic for DecimalPlaces Property:

"Note The DecimalPlaces property setting has no effect if the Format property is blank or is set to General Number."

So try modifying the field's Format property, to see if you can get what you want displayed.