When a copy activity is failed I just can't find which column caused it . Why you ms guys do not do us a favor to put the column name in the error msg ,this is really disgusting design.

su.chen 26 Reputation points
2021-03-02T07:41:03.123+00:00

the god damn it msg is always like this: Error details Error code 2200 Troubleshooting guide Failure type User configuration issue Details 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.InvalidOperationException,Message=The given value of type String from the data source cannot be converted to type nvarchar of the specified target column.,Source=System.Data,''Type=System.InvalidOperationException,Message=String or binary data would be truncated.,Source=System.Data,' Source Pipeline CopyPipeline_XXXXXX Obviously there error in it ,I've konwn this. So please tell me which column caused this OK? Which column got a string that can't be converted? For god sake would you please give a hint, cause there are so many columns got a string type, I just can't check it one by one .

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,646 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Mike McGuinness 26 Reputation points
    2022-01-28T21:12:32.183+00:00

    How does one troubleshoot this issue?

    "Operation on target XYZ CSV failed: Failure happened on 'Sink' side. 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.InvalidOperationException,Message=Column 'ABC' does not allow DBNull.Value.,Source=System.Data,'

    I have:

    • Verified that every row in the data source has a value for that column
    • Verified that "First row as header" is turned on for the source
    • I have turned on fault tolerance and still get the same error. 0/399,112 records imported
    • Have turned on logging for the copy activity and get nothing in the log except it says it picked up and processed the file.
    • Verified that all other non-null fields always have data

    How do you troubleshoot this issue? The error message given is so vague, and doesn't even point to a specific row.

    1 person found this answer helpful.

  2. JianleiShen-MSFT 1 Reputation point Microsoft Employee
    2021-05-11T03:35:44.463+00:00

    @su.chen Thank you for providing your feedback. The error msg is not generated from ADF but from SqlClient directly and we agree that it'd be more intuitive if it contains explicit info for user to troubleshoot. We will consider to enhance this by upgrading sql sdk ADF is using. Currently can't get an exact date of when this will be done due to other priority. Thank you again.


  3. Muhammad Tayyab Majeed 0 Reputation points
    2024-09-25T12:46:42.8066667+00:00

    I'm running a synapse pipeline to copy data from ODBC database to an Azure SQL Database. However, I'm seeing an error that suggests there might be invalid data causing the pipeline to fail. I'm auto-creating the table at the sink, so theoretically the schema should be transferred correctly. I've searched the MS documentation but haven't found anything helpful. Can someone provide assistance in fixing this error? The error details are as follows:

    { "errorCode": "2200", "message": "Failure happened on 'Sink' side. 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=OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].GROSS_AMOUNT'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=7339,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=7339,State=1,Message=OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].GROSS_AMOUNT'.,},],'", "failureType": "UserError", "target": "Copy data", "details": [] }


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.