Error while getting data from zoho invoices and to save to Azureblobstorage or Database

Question

Failure happened on 'Source' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][Zoho] (100) Error parsing JSON: Invalid value. while parsing string {"code":,"message":"success","invoices":[{"invoice_id":"1001793000000018023","ach_payment_initiated":false,"zcrm_potential_id":"","zcrm_potential_name":"","customer_name":"Ms. Elsa P","customer_id":"1001793000000018001","company_name":"Test","status":"paid","color_code":"","current_sub_status_id":"","current_sub_status":"paid","invoice_number":"INV-000001","reference_number":"","date":"2022-09-12","due_date":"2022-09-12","due_days":"","currency_id":"1001793000000000064","schedule_time":"","email":"elsa@Stuff .com","currency_code":"INR","currency_symbol":"₹","is_pre_gst":false,"template_type":"excel","no_of_copies":1,"show_no_of_copies":true,"is_viewed_by_client":false,"has_attachment":false,"client_viewed_time":"","invoice_url":"https://zohosecurepay.in/invoice/test60016723095/secure?CInvoiceID=2-baa7c161e8939280c99df084e4d23318e15486956f7628a640acd8bf3350251c9f6329d8a41c41fa2971c53296c39ae2484e343568a285d265268fdba479d682ed59...,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY000] [Microsoft][Zoho] (100) Error parsing JSON: Invalid value. while parsing string {"code":,"message":"success","invoices":[{"invoice_id":"1001793000000018023","ach_payment_initiated":false,"zcrm_potential_id":"","zcrm_potential_name":"","customer_name":"Ms. Elsa P","customer_id":"1001793000000018001","company_name":"Test","status":"paid","color_code":"","current_sub_status_id":"","current_sub_status":"paid","invoice_number":"INV-000001","reference_number":"","date":"2022-09-12","due_date":"2022-09-12","due_days":"","currency_id":"1001793000000000064","schedule_time":"","email":"elsa@Stuff .com","currency_code":"INR","currency_symbol":"₹","is_pre_gst":false,"template_type":"excel","no_of_copies":1,"show_no_of_copies":true,"is_viewed_by_client":false,"has_attachment":false,"client_viewed_time":"","invoice_url":"https://zohosecurepay.in/invoice/test60016723095/secure?CInvoiceID=2-baa7c161e8939280c99df084e4d23318e15486956f7628a640acd8bf3350251c9f6329d8a41c41fa2971c53296c39ae2484e343568a285d265268fdba479d682ed59...,Source=ZohoODBC_sb64.dll,'

Answer 1

Hello @Ranjana SG ,

Thanks for the question and using MS Q&A platform.

As per my understanding you are receiving above error message while trying to copy data from Zoho using ADF Zoho connector. Please correct me if I'm not clear.

My experience with Zoho connector is minimal and also, I don't have a Zoho instance to test/reproduce your scenario. But by looking at the error message you have shared, looks like the issue with the JSON data returned from Zoho service.

To validate the JSON, I tried copying the JSON content from the error message to identify whether JSON format returned is a valid one. I used this third-party free tool to validate it - https://jsonformatter.org/

And noticed that code column doesn't have any value and hence the parsing is failing with above error message, please see below for evidence.

If I add a value to code column, then the validation goes fine without any issue, hence this hints me that the data coming from Zoho isn't well formatted or not all the data is returned. Please see below:

I suspect something to do with Zoho service. But I would also recommend you try other tools to verify if the data returned is same as the one you see from ADF or not. And if you notice this issue is only with ADF then I would recommend filing support ticket for deeper analysis of the Zoho connector as it is in Preview.