data factory copy activity - select sql

Question

hello,

sql server table has columns with datatype float.

using the copy activity in datafactory, I retrieve the columns. i.e. in the source tab I have select * from tblMain

All the values in the columns have either 2 or 3 dp in sql server table

One column in particular which also has a float datatype has a value of 7124.28 for a particular key

but using the copy activity in the source where I have a sql query such as the following:

select * from tblMain I can see that particular column value in the source tab showing as 7124.279999999999

Note, all the other column values shown in the sql query of source tab are the same decimal places as sql server source except this very value of this column for a particular key.

For any other key the decimal places in the source tab are as shown in sql server.

Any suggestions why I see alot of decimal places for that very row of that very column?

Thank you

example:

sql server tblMain

ID columnAccount field1 field2 field3...

1 xx 23.33 7124.28 99.123

2 RR 28.3 56.2 77.565

...

in copy activity of datafactory in source tab I see

ID columnAccount field1 field2 field3...

1 xx 23.33 7124.279999999999 99.123

2 RR 28.3 56.2 77.565

...

Answer 1

Hi @arkiboys ,

Thanks for reaching out. Could you please share the schema of your source table highlighting the column that is having the issue? Also, would it be possible to share a screenshot of the source column value that is having issue in data factory including data factory source tab preview screenshot?

In general, Float is Approximate-number data type, which means that not all values in the data type range can be represented exactly.

Whereas Decimal/Numeric is Fixed-Precision data type, which means that all the values in the data type range can be represented exactly with precision and scale.

TEST 1: To double check if this issue is caused by the Float data type, I would recommend clone that source table, rename it, Alter the column that is creating the issue from Float data type to Decimal data type and try to use it ADF copy and preview the data to ensure if you see the same behavior as float data type as earlier or the data looks good as expected. If the data looks good, then the test confirms this issue due to the behavior of Float data type.

TEST 2: Or instead of cloning, while reading the data from source in your copy activity, try to use query option instead of table and do a query as below casting the float datatype column that is having the issue and see the behavior. Hopefully that helps to resolve the issue.

SELECT  CAST(field2 as decimal(20,12)) as newField2

Hope this info helps. Do let us know how it goes.

Thanks