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.

Question

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

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 .

su.chen 26 Reputation points

2021-03-02T08:29:00.487+00:00

by the way, this adf product style looks as if it was developed by Soviet Russia.

It is anyway strong enough and really hard to use with awful documents.
The only way to kown this is to utilize google to search answer in your azure docs. To search in your docs is a disaster , your azure doc search engine will show lots of rubbish answers with the only aim to enrage readers.

You made the support team busy answering questions which should be wrttien in docs , and the support team is the only reason that I continue to use this product.
Nasreen Akter 10,811 Reputation points Volunteer Moderator

2021-03-02T13:24:03.687+00:00

Hi @su.chen ,

Sorry to hear that. :(

There is a Settings --> Fault tolerance option in the CopyActivity where you can skip incompatible rows. And also you can Enable logging to get those rows in the log file.

From the error msg, it seems like you have one/more string data/column in the sourceDataset that exceeds the column size/length of the targetDataset e.g., if your column is defined as nvarchar(100) and you are trying to insert string with 200 chr, you could get this error. Would you please check the column length in the DB Table. Hope this helps!

Thanks!
su.chen 26 Reputation points

2021-03-03T02:38:13.723+00:00

Thank you anyway. I did exactly as your suggestion, and I did this way as long as I started use this adf.
I am sure I would finally find the column with the only problem of wasting lots of time. The column that caused failure could be shown in the msg but the developer did not care and leave this matter to users and support teams like you guys.

Sorry to bother you ,it's not your fault anyway thank you .
I am just tired of checking errors in no efficiency.
HimanshuSinha-msft 19,491 Reputation points Microsoft Employee Moderator

2021-03-11T18:04:31.71+00:00

Hello @su.chen ,

My sincere apoloziges about the poor the experience , we want to acknowledge your feedback & request you to please log the same here .
Product group does monitor the request and they can plan for the implementation in future .Once you log the feature request you will also be notified on the status of the request .

Please do share the url of the logged issue here so that other community members are aware of the same ..
Thanks
Himanshu

3 answers

Your answer

su.chen 26 Reputation points

2021-03-02T08:29:00.487+00:00

by the way, this adf product style looks as if it was developed by Soviet Russia.

It is anyway strong enough and really hard to use with awful documents.
The only way to kown this is to utilize google to search answer in your azure docs. To search in your docs is a disaster , your azure doc search engine will show lots of rubbish answers with the only aim to enrage readers.

You made the support team busy answering questions which should be wrttien in docs , and the support team is the only reason that I continue to use this product.
Nasreen Akter 10,811 Reputation points Volunteer Moderator

2021-03-02T13:24:03.687+00:00

Hi @su.chen ,

Sorry to hear that. :(

There is a Settings --> Fault tolerance option in the CopyActivity where you can skip incompatible rows. And also you can Enable logging to get those rows in the log file.

From the error msg, it seems like you have one/more string data/column in the sourceDataset that exceeds the column size/length of the targetDataset e.g., if your column is defined as nvarchar(100) and you are trying to insert string with 200 chr, you could get this error. Would you please check the column length in the DB Table. Hope this helps!

Thanks!
su.chen 26 Reputation points

2021-03-03T02:38:13.723+00:00

Thank you anyway. I did exactly as your suggestion, and I did this way as long as I started use this adf.
I am sure I would finally find the column with the only problem of wasting lots of time. The column that caused failure could be shown in the msg but the developer did not care and leave this matter to users and support teams like you guys.

Sorry to bother you ,it's not your fault anyway thank you .
I am just tired of checking errors in no efficiency.
HimanshuSinha-msft 19,491 Reputation points Microsoft Employee Moderator

2021-03-11T18:04:31.71+00:00

Hello @su.chen ,

My sincere apoloziges about the poor the experience , we want to acknowledge your feedback & request you to please log the same here .
Product group does monitor the request and they can plan for the implementation in future .Once you log the feature request you will also be notified on the status of the request .

Please do share the url of the logged issue here so that other community members are aware of the same ..
Thanks
Himanshu

Answer 1

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.

ravi 1 Reputation point

2022-08-17T06:48:16.477+00:00

if the sink is dedicated sql pool, try enabling staging and give adls as storage.

Answer 2

JianleiShen-MSFT 1 Microsoft Employee

@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.

Tamilkanal Selvaraj 0 Reputation points

2023-06-15T15:00:02.5766667+00:00

currently aim Also facing the exactly same issue ,Any Improvement about this issue

Operation on target ForEach1 failed:

Activity failed because an inner activity failed;

Inner activity name: Copy Azure file,

Error: 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=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,'
Harder Tim 0 Reputation points

2023-06-28T09:34:37.84+00:00

Hello,

is there a solution in the meantime?
Kröll, Fabian 0 Reputation points

2024-05-15T15:27:54.82+00:00

Any news on this please? My transformation of a local sql table (self hosted ir) to a json sink on an azure blob storage fails miserably after ~650.000 records, reporting an issue with a decimal conversion, but nowhere in the logs can I find any information about the affected record or at least column...

Answer 3

Muhammad Tayyab Majeed 0

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": [] }

Muhammad Tayyab Majeed 0 Reputation points

2024-09-25T12:47:11.25+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": [] }

Share via

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.

3 answers

Your answer