adf-sql bulk copy failed

KEERTHANA JAYADEVAN 66

@HimanshuSinha-msft
Failure happened on 'Sink' side. ErrorCode=SqlBulkCopyInvalidColumnLength,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=SQL Bulk Copy failed due to receive an invalid column length from the bcp client.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=The service has encountered an error processing your request.
This is what i am getting and i checked thoroughly and all my column lengths are matching

Bhargava-MSFT 29,266 Reputation points Microsoft Employee

2022-09-27T19:59:22.253+00:00

Hello @KEERTHANA JAYADEVAN ,

Welcome to the MS Q&A platform.

Please correct me if my understanding is wrong. You are using copy activity to copy the data to SQL and getting this error.

Please ensure all data types are mapped correctly, similar data types are available in the target, and also the length

This error is because the length of the data coming into the destination column is bigger than the column size defined. You can choose a data type that is large enough for your destination.

Also, to identify which row has encountered the problem, enable the fault tolerance feature on the copy activity. This can redirect problematic rows to the storage for further investigation.

Here is the documentation: https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-fault-tolerance

I hope this helps.
KEERTHANA JAYADEVAN 66 Reputation points

2022-09-28T05:17:14.987+00:00

In my target sql server, i have defined all datatypes as varchar NULL. Still how will i get length mismatch?
Bhargava-MSFT 29,266 Reputation points Microsoft Employee

2022-09-29T00:07:14.687+00:00

Hello @KEERTHANA JAYADEVAN ,
Thanks for the reply. I see a similar thread.They were able to resolve the issue by NVARCHAR. Can you please change your target datatype to Nvarchar(max) and see? (If your source is Unicode, then nvarchar(max) will resolve the issue)
As a troubleshooting step, could you please enable fault tolerance and see which column is having the issue here?
KEERTHANA JAYADEVAN 66 Reputation points

2022-09-29T09:15:45.527+00:00

I tried nvarchar max, its failing then also
Bhargava-MSFT 29,266 Reputation points Microsoft Employee

2022-09-29T19:15:02.737+00:00

Hello @KEERTHANA JAYADEVAN ,
Did you get a chance to enable fault tolerance and see which column is having the issue?
It seems like the issue is at the source end. I would suggest filing a support request, and a support engineer can look deeper into your source data to troubleshoot the issue further.
If you don't have a support plan, I can enable one-time free support for you to work closely on this matter.
I am looking forward to hearing from you.
Bhargava-MSFT 29,266 Reputation points Microsoft Employee

2022-10-04T23:44:55.487+00:00

Hello @KEERTHANA JAYADEVAN ,
I am just checking in to see if you need any further help here.

Share via

adf-sql bulk copy failed