Oracle Copy data activity failed in ADF

Question

Hi,

I am trying to Load oracle table using Copy data activity in ADF. I am getting below failure .. I have verified all the schema and data and it is intact with table structure but still failing. Please suggest your thoughts on it.

Failure happened on 'Sink' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver]Error tolerance exceeded. Bulk load operation terminated.
ERROR [22001] [Microsoft][ODBC Oracle Wire Protocol driver]String data, right truncated. Row discarded.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=Microsoft.DataTransfer.ClientLibrary.Odbc.Exceptions.OdbcException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver]Error tolerance exceeded. Bulk load operation terminated.
ERROR [22001] [Microsoft][ODBC Oracle Wire Protocol driver]String data, right truncated. Row discarded.,Source=msora28.dll,'

Thanks,
naveen

Answer 1

Hi Mike,

I have tried keeping EnableBulkLoad=0 in connection properties and found the exact error message. Where one field was getting value more than its max length. Thanks....

Answer 2

Hello @Jaganathan, Naveen ,
Thanks for the question and using MS Q&A platform.

As I understand you are writing to Oracle and want help with that error message.

By the sound of String data, right truncated. I would think some text is larger than the column expects. However, I did some searching and found similar cases:

error-22001-microsoftodbc-oracle-wire-protocol-dri.html
adf-copy-activity-oracle-error-error-tolerance-bulk-load-vishwakarma
adf-copy-activity-to-oracle-error-error.html

The solution seems to be increasing write batch size 10,000 -> 50,000

Please let me know if this helps.

If this works, then it sounds like the length of a complete chunk of data was larger than the chosen size. Like, sticking a long object in a truck only for the end to stick out beyond the tailgate.

Please do let me if you have any queries.

Thanks
Martin

---

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 3

BTW the 'Old School' way to get data from Oracle/Otherdb when something went wrong nobody could figure out is to download the data to a text file, then upload that text file to SqlServer. Then enjoy your holiday.