0

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?

bmickey
  • 37
  • 1
  • 6
  • Maybe you can split your issue in half. Will it still fail if you choose a different sink, e.g. to Azure Storage as a file. And maybe you can check the file if it contains something out of the ordinary. – Koen Oct 24 '21 at 19:28
  • The issue might be that the data in column number 20 is larger than expected, see https://stackoverflow.com/questions/10442686/received-an-invalid-column-length-from-the-bcp-client-for-colid-6 for a comparable issue. – Koen Oct 24 '21 at 19:35
  • If you want to view what the REST API is returning you could just use the Web activity in the pipeline just for debugging. Make sure you're making the same call as the Copy activity. The API output will appear in the Output tab of ADF, assuming this error is not coming from the API itself, in which case you won't see anything. You could also use a client like Postman or just call it in the browser, depending on the API. – wBob Oct 26 '21 at 14:17

1 Answers1

0

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

Abhishek K
  • 3,047
  • 1
  • 6
  • 19