Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,661 questions
adf-sql bulk copy failed
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(211.20000000000002, 69%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EKJ%3C/text%3E%3C/svg%3E)
Keerthana J
71
Reputation points
@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
Azure Data Factory
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(86.4, 36%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBM%3C/text%3E%3C/svg%3E)
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator2022-09-27T19:59:22.253+00:00 Hello @Anonymous ,
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 J 71 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 31,261 Reputation points Microsoft Employee Moderator
2022-09-29T00:07:14.687+00:00 Hello @Anonymous ,
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 J 71 Reputation points
2022-09-29T09:15:45.527+00:00 I tried nvarchar max, its failing then also
-
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator
2022-09-29T19:15:02.737+00:00 Hello @Anonymous ,
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 31,261 Reputation points Microsoft Employee Moderator
2022-10-04T23:44:55.487+00:00 Hello @Anonymous ,
I am just checking in to see if you need any further help here.
Sign in to comment
Sign in to answer