Copy Activity erroring 'received an invalid column length'

Question

Copy Activity erroring 'received an invalid column length'

JW_6688 1

copy activity failed when encounter one record with unexpected length, is there a way to log the record instead of failing the activity? configured as 'Skip incompatible rows' already and it only for mismatch with data type.
if can log data truncation and incompatible data type together would be good.

Error message:

ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Received an invalid column length from the bcp client for colid 9.,Source=.Net SqlClient Data Provider,SqlErrorNumber=4815,Class=17,ErrorCode=-2146232060,State=1,Errors=[{Class=17,Number=4815,State=1,Message=Received an invalid column length from the bcp client for colid 9.,},],'

Any help is appreciated

svijay-MSFT 5,256 Reputation points Microsoft Employee Moderator

2021-10-07T16:24:57.957+00:00
Hello @JW668-2813,

iI had tested the behavior at my end. When I had set the Fault tolerance to 'Skip Incompatible rows'.

The rows that had lengthy columns got skipped and got logged below in the below fashion.

I was trying the above in the Copy activity. Pls let me know if you're making use of other activities.

if there is a possibility to modify at the sink(sql), the other option would be to alter the column size at the sink side

alter table persons alter column <column_name> nvarchar(max)
JW_6688 1 Reputation point

2021-10-07T18:44:39.657+00:00

I tried with a very simple data set as below and still not getting truncation record in the log. I attached the code in Json format here. please point out which part I missed

Target table:
CREATE TABLE [dbo].AA_person ON [PRIMARY];

138648-person-copyactivity.txt
svijay-MSFT 5,256 Reputation points Microsoft Employee Moderator

2021-10-12T14:05:32.123+00:00

Hello @JW_6688 ,

I did check JSON & compared against mine. I did not see anything wrong. and I had tried the same at my end. Unfortunately, I am not able to repro the issue. I ll try one more time with the above schema and update you.
svijay-MSFT 5,256 Reputation points Microsoft Employee Moderator

2021-10-19T14:53:19.167+00:00

Hello @JW_6688 ,

Can you please confirm whether you are making use of a SQL Managed Instance as opposed to Azure SQL?

Your answer

svijay-MSFT 5,256 Reputation points Microsoft Employee Moderator

2021-10-07T16:24:57.957+00:00

Hello @JW668-2813,

iI had tested the behavior at my end. When I had set the Fault tolerance to 'Skip Incompatible rows'.

The rows that had lengthy columns got skipped and got logged below in the below fashion.

I was trying the above in the Copy activity. Pls let me know if you're making use of other activities.

if there is a possibility to modify at the sink(sql), the other option would be to alter the column size at the sink side

alter table persons alter column <column_name> nvarchar(max)
JW_6688 1 Reputation point

2021-10-07T18:44:39.657+00:00

I tried with a very simple data set as below and still not getting truncation record in the log. I attached the code in Json format here. please point out which part I missed

Target table:
CREATE TABLE [dbo].AA_person ON [PRIMARY];

138648-person-copyactivity.txt
svijay-MSFT 5,256 Reputation points Microsoft Employee Moderator

2021-10-12T14:05:32.123+00:00

Hello @JW_6688 ,

I did check JSON & compared against mine. I did not see anything wrong. and I had tried the same at my end. Unfortunately, I am not able to repro the issue. I ll try one more time with the above schema and update you.
svijay-MSFT 5,256 Reputation points Microsoft Employee Moderator

2021-10-19T14:53:19.167+00:00

Hello @JW_6688 ,

Can you please confirm whether you are making use of a SQL Managed Instance as opposed to Azure SQL?

Share via

Copy Activity erroring 'received an invalid column length'

Your answer