Copy Activity is failing with the following error

Question

{ "errorCode": "2200", "message": "ErrorCode=FailedDbOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'PdwManagedToNativeInteropException ErrorNumber: 46724, MajorCode: 467, MinorCode: 24, Severity: 20, State: 2, Exception of type 'Microsoft.SqlServer. DataWarehouse.Tds.PdwManagedToNativeInteropException' was thrown.',Source=,''Type=System.Data.SqlClient.SqlException,Message=PdwManagedToNativeInteropException ErrorNumber: 46724, MajorCode: 467, MinorCode: 24, Severity: 20, State: 2, Exception of type 'Microsoft.SqlServer.DataWarehouse.Tds.PdwManagedToNativeInteropException' was thrown., Source=.Net SqlClient Data Provider,SqlErrorNumber=100000,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=100000,State=1,Message=PdwManagedToNativeInteropException ErrorNumber: 46724, MajorCode: 467, MinorCode: 24, Severity: 20, State: 2, Exception of type 'Microsoft.SqlServer.DataWarehouse.Tds.PdwManagedToNativeInteropException' was thrown.,},],'", "failureType": "UserError", "target": "hana_ODS_DEV" }

Can anyone please let me know the main cause for this error? I am trying to migrate the data from SAP HANA onto Azure SQL Dataware house

Answer 1

This error occurs when one or more columns of destination have defined size lesser than the data which you are trying to copy. Just increase the size of the columns and your ADF will run smoothly. Sharing the snippet query to increase the size of the column in ADW,

alter table table_name alter column columne_name datatype NULL

Note -

1. You cannot decrease the size of columns in ADW. You can just increase the size.
2. The error message does not give details about the column or row because of which copy activity fails. You need to figure that out manually.

Let me know if this works.

Answer 2

Well, I found an answer for this issues. Sometimes it might be the issue with yor source data types and column data types. If you are migrating data from any source to Azure SQL without any BLOB in between and facing this issues, and want to know the exact issue where it is lying, click on allow poly base in your sink, enable the staging in settings and give a path for blob staging storage, debug it.... It shows you the exact cause of where it is causing you the error.

Answer 3

I got the same error and both the above points mentioned are useful to find a solution for this.

This is what we did

We got this error while copying data from blob storage to azure data warehouse table. We had already set up the logging and fault tolerance to "skip and log incompatible rows"and even after that we were getting the error and the activity terminated as a failure which is indeed because the size of the source column values was greater than the target column size. We then set up a staging area inside the blob storage, somehow the load skipped the incompatible rows and processed the data.

Not sure if it was because of logging (logically should have been) or the staging property or combination of both.

Incompatible rows and the file/s responsible for it were stored inside the Log/polybase/.../rejectedrows folder.