Getting Error with Polybase in Copy Command and at same time Bulk Insert works?

Question

Hi,

I was getting below mentioned error with polybase, and unable to load data but the same file is getting loaded with bulk insert

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: String or binary data would be truncated.,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: String or binary data would be truncated.,},],

Can someone help ?

Thanks,

Yash.

Answer 1

Hello @Mutthuluru Yashwanth Sai,

This error (String or binary data would be truncated) generally means a type mismatch between source and target. The max length of the target is less than the actual source data. As a result, data is truncated, returning this error.

Please try the below.

1. Check the length of actual data in the source and the length of the column in the target table.
2. The max length of the intermittent external table in polybase(behind the scenes, PolyBase creates an External Table) is nvarchar(4000). Therefore, if the max length of the source column is greater than nvarchar(4000), polybase option can’t be used.

If this is your case, please try to use the bulk insert option, not the polybase or copy into option in copy activity.

[https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-sql-data-warehouse?tabs=data-factory#use-polybase-to-load-data-into-azure-sql-data-warehouse