Error converting data type VARCHAR to DECIMAL

Question

I run an export from an Oracle table to csv file and have the csv on data lake. I scripted the Synapse destination table columns datatypes so they are correct. While loading with copy data from lake to Synapse I get:
PolybaseOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error happened when loading data into SQL Data Warehouse. Operation: 'Polybase operation'.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: HadoopSqlException: Error converting data type VARCHAR to DECIMAL.,Source=.Net SqlClient Data Provider,SqlErrorNumber=107090,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=107090,State=1,Message=HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: HadoopSqlException: Error converting data type VARCHAR to DECIMAL.,},],'"

1. There is no reference in the output log regarding the column that fails. How do I know which column fails?
2. As per (https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-sql-data-warehouse?tabs=synapse-analytics#staged-copy-by-using-polybase), if I use staging it should fix if the source data is not natively compatible with Polybase. Is there any way to still use “Copy data”, or I should better chance to data flow (and add derived columns decimal for any datatype transformation)

Answer 1

Hi @KranthiPakala-MSFT , most likely your solution would have covered the majority of cases. However, it was not solving my particular case, since the file was gzip compressed and in Synapse it needed to be unzipped on staging and then load. However the loading failed. this was a large table (1 bil records, approx 180 GB). Really the way to fix, I used below:

1. scripted Synapse stage table by change all decimal columns with varchar (like 256) , larger length.
2. just used copy command which worked on this staging table.
3. than I could test this table for any row null, not decimal, etc in SQL and CTAS to final table. Strangely I did not find any column that would have failed conversion from varchar to decimal, just think that the file was too large (approx 200 GB, over 1 bil records). but applying the previous method, the time decreased, and did not have to use staging too.

Thank you,

Answer 2

Hello @HSever ,

Thanks for the question and using MS Q&A platform.

For logging incompatible rows:

• While loading data into Azure Synapse Analytics using PolyBase, you can configure PolyBase's native fault tolerance settings by specifying reject policies via polyBaseSettings in copy activity. You can still enable redirecting PolyBase incompatible rows to Blob or ADLS as normal as explained in this document - Fault tolerance of copy activity in Azure Data Factory and Synapse Analytics pipelines

Workaround to overcome the error:

• After logging the incompatible rows please validate if the source data column (varchar) is having empty values which were trying to load to Synapse Decimal type column. This error usually occurs when trying to load empty values to a Decimal type of column in Synapse. To overcome such issues, the solution is to unselect Use type default option (as false) in copy activity sink -> PolyBase settings. USE_TYPE_DEFAULT is a PolyBase native configuration, which specifies how to handle missing values in delimited text files when PolyBase retrieves data.

Here is the reference document: Azure Synapse PolyBase Troubleshooting - Loading to Decimal column

Hope this info helps.

---

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how