Copy Activity with stored procedure rounds up decimals

3
votes

I am using an Azure Data Factory to get data from an on prem database to an Azure sql database.

I am doing it in 2 steps:

  1. Copy to blob
  2. Insert into azure using a copy activity that runs a stored procedure.

The problem i have is the decimal is rounded up and the destination values are different than source.

For the entire process i have defined 3 datasets:

  1. SQLDataset - the structure node is {... "name" : "MyColumn", "type":"Decimal"}.
  2. BlobDataset - the structure node is the same as above
  3. AzureSQLDataset - the structure node is the same

On the SQL database, i have a user defined table type as follows:

CREATE TYPE dbo.myType TABLE (...MyColumn decimal(28,10) null)

The stored procedure does an insert or an update and no data transformation. However, when i query for MyColumn value, the values end up being rounded up.

If source had a value of 266222451894.019990000 , target has a value of 266222451894.0200000000

Am i missing something ?

1
azureazure-data-factory
I have investigated this with Microsoft support and we have identified it as a bug. There are issues when converting decimals with high precision and loads of numbers. – Mihai Tibrea

1 Answers

0
votes

If you want to a precise numeric value, your best bet is to ship the numeric value out as a string.