ADF pipeline not able to read DECIMAL(36,0) value from Parquet file

Question

We're using a copy activity to copy parquet file data into our managed instance SQL server.

The source is using a SQL Serverless query to read the parquet files. There's a new column coming through that is bringing in large values and causing failures e.g. 28557632721941551956925858310928928

There isn't any problem querying it straight out of Azure Data Studio using SQL Serverless.

Here's the error message:

{ "errorCode": "2200", "message": "Failure happened on 'Source' side. ErrorCode=UserErrorInvalidDataValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to read data from source.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.OverflowException,Message=Conversion overflows.,Source=System.Data,'", "failureType": "UserError", "target": "Stage Parquet File Data", "details": [] }

I also tried using a parquet file dataset for my source. This is the failure I received:

{ "errorCode": "2200", "message": "ErrorCode=ParquetBridgeInvalidData,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column gwcbi___seqval of primitive type FixedLenByteArray, original type Decimal contained an invalid value for the given original type.,Source=Microsoft.DataTransfer.Richfile.ParquetTransferPlugin,'", "failureType": "UserError", "target": "Stage Parquet File Data", "details": [] }

This looks like a serious limitation of Synapse/ADF pipelines. Any ideas?

Thanks, Jason

What is the mapping setting? Is that column `wcbi___seqval` of type `decimal`? — Aswin, Dec 05 '22 at 05:55
Yes. If import the schema it returns a 'decimal' for the field. — Jason Campbell, Dec 06 '22 at 18:28
Is both the source and sink having same schema? Both have decimal data type in mapping? — Aswin, Dec 13 '22 at 09:42

score 0 · Answer 1 · answered Dec 14 '22 at 00:15

A conversion overflow means that the value was too big for the datatype it's trying to be stored in. Decimals with precision greater than 28 (BigDecimals) are not supported in ADF copy activity which is why the above issue.

As a workaround you may try casting/converting the datatype to other (for example String/varchar)

But if you have feedback to improve the ADF product, please feel free to log it in ADF IDEAS forum here - https://feedback.azure.com/d365community/forum/1219ec2d-6c26-ec11-b6e6-000d3a4f032c

ADF pipeline not able to read DECIMAL(36,0) value from Parquet file

1 Answers1