Rounding error: Between flat file connection manager Source & OLE DB Connection Destination

I have a Rounding error: Between flat file connection manager Source & OLE DB Connection Destination (SQL Server 2005) in my Dataflow.

File looks like this lets call column names Col A,B,C,D

70410000 RD1 1223631.92 196042.42
70329000 ICD 11025.84 3353.88
71167300 COL 104270.59 24676.96

flat file connection manager settings: first row Column names then Advanced tab Col A float , Col B float , Col C string ,Col D float ,

OLE DB Connection Destination (SQL Server 2005)

CREATE TABLE [dbo].[PT_CUST_ABR](

[PARTY_NO] [float] NULL,

[PARTY_NAME] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[TELECOMABR] [float] NULL,

[GENIABR] [float] NULL,

Problem: ColA (Source) Rounding error to PARTY_NO (Destination)

I have a field of text of in a flat file that the flat file connection manager Source picks up correctly “70000893”

However when it gets the OLE DB Connection Destination the data has changed to 70000896. That’s before its even Written to the database.

The only clue that something is wrong in the middle is the great Data viewer shows the number as 7.000009E+07

Other clues looking at the data it appears there is a rounding error on only the number that dont end in 00

ColA (Source) PARTY_NO (Destination)
71167300 71167296
70329000 70329000
70410000 70410000

Any ideas people?

Thanks in advance

Dave

Float type by definition is not precise, it holds about 7 decimal digits, so it is expected.

If you want to keep exact values, you should use types that are big enough to hold them. E.g. Int for ColA and Decimal for ColC & ColD.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_6r3g.asp

|||

Thanks Michael--already tried that --turned out to the XML file in SSIS had my old settings locked in and my new settings in the flat file connection manager Source did not take effect

I fixed the problem by changing the datatype to string in the flat file connection manager and bigint in the SQL Server 2005 table. However I needed to delete all the objects in the dataflow task and re-recreate them again.--this fixed the XML metadata problem

Dave