Configuring a linked service between zoho (CRM) and Azure Data Factory

user_18633136 0
Viewed 188

I am trying to configure a linked service in Azure Data Factory (ADF) in order to load the ZOHO data to my SQL database. I am using a REST API linked service for this which successfully connects to ZOHO. I am mainly struggling to select the proper relative URL. Currently I use the following settings:

Base URL: https://www.zohoapis.eu/crm/v2/
Relative URL: /orgxxxxxxxxxx

When I try to preview the data, this results in the following error:

Error occurred when deserializing source JSON file ''. Check if the data is in valid JSON object format. Unexpected character encountered while parsing value: <. Path '', line 0, position 0. Activity ID: 8d32386a-eee0-4d2a-920a-5c70dc15ef06

Does anyone know what I have to do to make this work such that I can load all required Zoho tables into ADF?

crm zoho azure-data-factory rest json
1 Answers

As per Microsoft official document:

The Zoho connector is supported for the following activities:

1. Copy activity with supported source/sink matrix
2. Lookup activity

REST API doesn't support Zoho Connector.

You are expecting a JSON file as source but your Sink is Azure SQL Database. You need to change your approach.

You can use directly use Zoho Linked Service in ADF to fetch the data from zoho (CRM).

You can copy data from Zoho to any supported sink data store. This connector supports Xero access token authentication and OAuth 2.0 authentication.

You need to use Copy activity to fetch the data from Zoho (CRM) and store it in Azure Storage account. Once data received, you need to Denormalize the JSON file to store it in Azure SQL Database. Use data flow activity to denormalize the data using flatten transformation.

Use the flatten transformation in mapping data flow to take array values inside hierarchical structures such as JSON and unroll them into individual rows. This process is known as denormalization.

Learn more here about Flatten transformation in mapping data flow.

Once flatten done, you can store the data in Azure SQL Database.

user_159690410
Related