CODEWITHSUNDEEP
azure-pipelinesazure-data-factory
bmickey
bmickey

Reputation: 47

ADF Copy Activity Failing Source REST, SINK SQLMI database. Invalid Column Length from the bcp client error?

I'm trying to troubleshoot a Pipeline I created that has a Copy Data activity. The source is a REST API and the Sink is a Azure SQL Managed Instance. I have pagination rules setup so that it iterates through the data and reads it all in but the Pipeline is failing. IF I turn off pagination and only read the 1st 100 rows it does not fail and sinks the first 100 rows to database.

Here is my error: 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 20.,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 20.,},],'

My question is, it says, colid 20. Is that colid 20 from the Mapping in Mappings tab in my pipeline OR colid 20 in that the REST API returns? Is there a way to figure out what field/value is causing the issue, the error just seems too vague to be helpful! Would it be possible to do some truncation of the source fields in the case it's longer than the database table datatypes in the mapping of this copy activity?

Upvotes: 0

Views: 1435

Answers (2)

Stephen Kusen
Stephen Kusen

Reputation: 1

I just ran into this error. I was querying data from a view, and landing it to a table. When I ran INSERT INTO []..SELECT.. I got a truncation error on one of the columns on the destination table. ADF simply was complaining with 'Received an invalid column length from the bcp client for colid'

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 49.,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 49.,},],'

The source of the "Copy data" task was a SQL Server query against a DB view that had a three-part reference, such as MyDB.dbo.MyTable1. The 'Sink' was a SQL Server table.

When I ran:

INSERT INTO [MyDestinationDb].[dbo].[MyDestinationTable] ([column1,2,3...etc])
SELECT [column1,2,3...etc] FROM MyDB.dbo.MyView1

One of the columns returned a truncation error. After updating the column to be a longer varchar length, the original error Received an invalid column length from the bcp client for colid went away.

Upvotes: 0

Abhishek Khandave
Abhishek Khandave

Reputation: 3228

Your first question is to identify which column is causing this issue.

As the below error message is suggesting, looks like issue is at Source column.

Message=Received an invalid column length from the bcp client for colid 20

Your Second question is, how to alter column in ADF.

To alter columns of dataset, use Dataflow.

Here you can use Derived column and Select statement as shown below

enter image description here

In Derived columns, you can change datatype and also you can explore many options in expression builder.

Here I have changed datatype of Column Salary from int to String.

enter image description here

As suggested by Koen in comments, please go through this link once

Upvotes: 0

Related Questions