ADF database copy error: Unexpected error encountered filling record reader buffer

iw 101 Reputation points
2021-05-17T07:04:57.223+00:00

Hello,
I am trying to copy data from an Azure SQL database into a Azure Synapse using datafactory and polybase.
Initially, I was able to copy 18 out of 20 tables successfully. The two initial failures were around loading NULL values into a decimal column using Polybase. I was able to resolve this particular issue on one of the two remaining tables by unselecting "Use type default" option (as false) in copy activity sink as per https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-sql-data-warehouse#staged-copy-by-using-polybasel

I tried doing this on the second of the two remaining tables and now encounter the following error:

ErrorCode=FailedDbOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error happened when loading data into SQL Data Warehouse.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: HadoopExecutionException: The column [9] is not nullable and also USE_DEFAULT_VALUE is false, thus empty input is not allowed.,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: HadoopExecutionException: The column [9] is not nullable and also USE_DEFAULT_VALUE is false, thus empty input is not allowed.,},]

I am unable to find anything related to this apart from https://knowledge.informatica.com/s/article/619319?language=en_US but am having trouble relating this back to what I'm doing in Data Factory.
I have checked columns 8,9,and 10 in the table in both the source Azure SQL database and the destination Synapse database and the column definitions are the same. Column 8 is NULLABLE, and column 9 is NOT NULLABLE in both source and destination.

Why am I getting this message?
Thanks

Azure SQL Database
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,365 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,533 questions
0 comments
Accepted answer
  1. iw 101 Reputation points
    2021-05-18T22:36:08.44+00:00

    I figured out the issue yesterday afternoon. The table in the Azure SQL database that is the source has some empty strings in a column that is not nullable in both the source and destination. When using Polybase to copy the data across, these empty strings get converted to NULL values which are not allowed in the column.
    I set the destination column to allow NULLs and the data loads successfully.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee
    2021-05-18T22:09:13.76+00:00

    Hi @iw ,

    Welcome to Microsoft Q&A forum and thanks for reaching out. It seems like the issue could be with source data. To further narrow down the issue, could you please try using skip incompatible rows option (Fault tolerance) in copy activity and see which particular rows are failing?

    97711-image.png

    Related Docs:
    https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-fault-tolerance
    https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-sql-data-warehouse#use-polybase-to-load-data-into-azure-synapse-analytics

    Do let us know your observations.

    Thanks

    0 comments