question

suchen-7727 avatar image
0 Votes"
suchen-7727 asked ravi-7044 commented

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.

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
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

Hi @suchen-7727,

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!

0 Votes 0 ·

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.

0 Votes 0 ·

Hello @suchen-7727,

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


0 Votes 0 ·
JianleiShen-MSFT avatar image
0 Votes"
JianleiShen-MSFT answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MikeMcGuinness-6428 avatar image
0 Votes"
MikeMcGuinness-6428 answered ravi-7044 commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·