Read / Write Zoho CRM data using SSIS REST API Call

Introduction

UPDATE: ZappySys has released a brand new API Connector for Zoho CRM Online which makes it much simpler to Read/Write Zoho CRM Data in SSIS compared to the steps listed in this article. You can still use steps from this article but if you are new to API or want to avoid learning curve with API then use newer approach.

Please visit this page to see all Pre-Configured ready to use API connectors which you can use in SSIS API Source / SSIS API Destination OR API ODBC Driver (for non-SSIS Apps such as Excel, Power BI, Informatica).

In this post, you will learn how to read / write Zoho CRM data using SSIS (Drag and drop approach without any coding).  We will use SSIS JSON/ REST API Source to extract data from Zoho API and use SSIS Web API Destination to write data to Zoho.

We will look at step-by-step instructions to read Zoho CRM data (e.g. Leads, Accounts, Contacts) and load into SQL Server Database Table. In the next section, we will look at Write scenario too.

Now let’s look at step-by-step approach to call Zoho API  using SSIS.

 

Prerequisites

Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:
  1. SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
  4. (Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.

Authentication Using Zoho V2 REST API (OAuth 2.0)

Zoho recently released v2 API. It uses OAuth 2.0 instead of static Token based (described in the next section). OAuth approach is more secure an recommended. If you must use static token-method then check next section. Zoho may discontinue Token based method in future.

Register Zoho OAuth App and get ClientID/ Secret (*** MUST READ ***)

Check this article to register your OAuth App and obtain Client ID  /Client Secret

Create Zoho OAuth Connection in SSIS (for API call)

Once you create Zoho OAuth App now its time to call some Zoho API. We will use ZappySys REST API Task to make simple API call. In the next section we will see how to load data in SQL Server.

  1. Open SSIS Package and go to SSIS Designer (Control Flow)
  2. Drag ZS REST API Task from Control Flow SSIS Toolbox as below
    Drag and drop SSIS REST API Task from SSIS Toolbox

    Drag and drop SSIS REST API Task from SSIS Toolbox

  3. Double click the task to configure.
  4. Change URL Access mode to URL from Connection
  5. Enter API URL you like to call. For example you can enter below
  6. Select New ZS-OAUTH connection from Connection drop down
    Call Zoho REST API using OAuth 2.0

    Call Zoho REST API using OAuth 2.0

  7. Configure your OAuth connection General Tab as below
    1. OAuth Provider: Custom
    2. OAuth Version: OAuth2
    3. Client Id , Client Secret
    4. Enter One Scope Per Line (For more info on scope read here)
    5. Auth URL : https://accounts.zoho.com/oauth/v2/auth
    6. Token URL:  https://accounts.zoho.com/oauth/v2/token
  8. Configure Advanced tab as below
    1. Enter Callback URL : https://zappysys.com/oauth
    2. Enter Extra Attribute for /Auth : access_type=offline&prompt=consent
      This is needed to obtain refresh_token (if you ommit this then only access_token returned which cannot be renewed once expired after 1hr)

  9. Now go back to general tab and Click Generate Token.
    Generate OAuth Token for Zoho CRM REST API Access

    Generate OAuth Token for Zoho CRM REST API Access

  10. Click OK to save the connection UI

Call Zoho API using OAuth Connection

  1. On the REST API Task click Test Request / Response
    SSIS REST API Task - Call Zoho REST API in SSIS (OAuth 2.0)

    SSIS REST API Task – Call Zoho REST API in SSIS (OAuth 2.0)

Read data from Zoho Table in SSIS JSON Source

In this section we will learn how to read data from Zoho using SSIS JSON Source. We will use OAuth connection created in earlier section.

Now lets look at step by step example of reading Zoho CRM data using SSIS (e.g. read Leads, Accounts, Contacts, Invoice etc). Zoho provides JSON API for read action so we will Use SSIS JSON Source for this purpose. Full sample package is attached at the end of this article but see below steps for high level configuration.

Configure JSON Source

So below steps shows example of reading from Zoho Lead modules using v2 records API Call.

  1. Open SSIS Package
  2. Drag data flow task from Control flow SSIS Toolbox
    Drag data flow from SSIS Toolbox

    Drag data flow from SSIS Toolbox

  3. Go to data flow and drag ZS JSON Source from SSIS Toolbox
    Drag and drop SSIS JSON Source (REST API or File)

    Drag and drop SSIS JSON Source (REST API or File)

  4. Double click JSON Source to configure. Enter URL as below. You can supply few more parameters. Click Here to get full list of parameters and usage. In below example we are reading records from Leads Module but you can replace Leads to something else (e.g. Accounts). Refer to above help link for available Modules.
  5. Check Use Credentials. Select OAuth connection manager we created in the previous section.
  6. Under filter options tab, enter Array Filter as   $.data[*]
  7. Now click Preview to see your data
    Read data from Zoho API v2 using SSIS - OAuth (Use Last Modified Date for Incremental Extract)

    Read data from Zoho API v2 using SSIS – OAuth (Use Last Modified Date for Incremental Extract)

  8. Click OK to save UI. In the next section we will see how to setup pagination and incremental extract of Zoho records. After that we will see how to load data into SQL Server Table.

Zoho API Pagination (Read all rows)

Just like most API zoho also uses pagination. See below for how to configure pagination in JSON Source for Zoho API.

  1. Double click JSON Source
  2. Go to the Pagination Tab and select URL Parameter Mode
  3. For Page Num Indicator Enter page
  4. For Last Page Detection select Detect last page based on status code
  5. In the Status code field
    enter 304 (if you added If-Modified-Since header for incremental extract – See next section)
    enter 204 (if you do not add If-Modified-Since)

Once you configure this way now your API will return all records until last page is detected. For more information on pagination check this article

Paginate Zoho API records

Paginate Zoho API records

Incremental Extract based on Last Modified Date/Time field

Each module in Zoho has Last Modified By and LastModified Date/Time fields. You can filter extracted data for incremental extract by supplying If-Modified-Since  HTTP header along with your request. Here is how to do.

  1. On the JOSN Source, click Raw Edit above HTTP Headers Grid.  We can define Header to extract data after last modified date. Skip this step if you like to extract all records.
    For example to read Lead records which are modified after 12/31/2019 you can enter header like below. Date/Time is UTC. When record is created Modified date is set as creation date too so this will include created records too.
    If you have local time saved in a variable and you like to use SSIS variable then you can use below way. In this example we converted Local time to UTC.

Loading Zoho data into SQL Server Table / Other Target

Now let's look at how to load data into target like SQL Server, Oracle or Flat File. In below example we will see loading data into SQL Server database but steps may remain same for other targets which can be accessed using OLEDB Drivers (e.g. Oracle).
  1. Inside Data Flow, Drag and drop Upsert Destination Component from SSIS Toolbox
  2. Connect our Source component to Upsert Destination
  3. Double click Upsert Destination to configure it
  4. Select Target Connection or click NEW to create new connectionConfigure SSIS Upsert Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS Configure SSIS Upsert Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS
  5. Select Target Table or click NEW to create new table based on source columns
  6. Click on Mappings Tab to Auto map columns by name. You can change mappings as you need SSIS Upsert Destination - Columns Mappings SSIS Upsert Destination - Columns Mappings
  7. Click OK to Save Upsert Destination Settings
  8. That's it, You are now ready to run data flow. NOTE: If you wish to debug data flow and see records when you run, add data viewer by right click on blue arrow > Click Enable Data Viewer
  9. To execute data flow, Right click anywhere inside Data Flow Surface and click Execute Task
 

Write data to Zoho CRM using OAuth 2.0

If you like to load data from any source (i.e. SQL Server) to Zoho CRM then you can use Records API (POST). Use combination of the following Transforms  / Components. Later in this article we have some more examples (Older API) which may give you one more ways to load data into Zoho (Use of Template Transform).

Basically you have to build POST Body using JSON Generator Transform or Template Transform and then use that data in Web API Destination to call POST API request to push data to Zoho.

Create JSON for POST request (Single dataset pattern)

Create JSON for POST request (Single dataset pattern)

 

Insert data to Zoho Table using SSIS (Lead, Account Modules) - Bulk Load using POST API call

Insert data to Zoho Table using SSIS (Lead, Account Modules) – Bulk Load using POST API call

Authentication Using Zoho Tokens

If you don’t want to use a newer version of Zoho API (v2) then you can use Token based Authentication method.

Token based method will be depreciated soon so avoid it if possible. Use OAuth 2.0 described in the previous section whenever possible. 

In Zoho Portal perform following steps to obtain API token

  1. Click on Setup (Toolbox icon at the top-right corner)
  2. Search for “API” > Select CRM API  or find under Developer space > APIs
  3. Click on Gear icon found above Usage Panel (small icon so find carefully)
  4. Click on Authentication Token generation
  5. Specify the APP Name and Click Generate
  6. This will direct to the new page which may contain text like below. Just copy AUTHTOKEN part (i.e. 8a08xxxxxxxxxx13630d )
  7. Now use the newly created auth token to your API call.
How to get Zoho CRM API Token(For SSIS or other app integration)

How to get Zoho CRM API Token (For SSIS or other app integration)

 

Read data from Zoho using SSIS (Export Zoho CRM data to SQL Server Table)

Now lets look at step by step example of reading Zoho CRM data using SSIS (e.g. read Leads, Accounts, Contacts, Invoice etc). Zoho provides JSON API for read action so we will Use SSIS JSON Source for this purpose. Full sample package is attached at the end of this article but see below steps for high level configuration.

So below steps shows example of reading Zoho Lead modules using getRecords API Call.

  1. Goto SSIS > Variable and create a new string variable called token. Enter your Zoho API token as value (see previous section to get API token)
  2. Drag data flow task from Control flow SSIS Toolbox
  3. Go to data flow and drag ZS JSON Source from SSIS Toolbox
  4. Double click JSON Source to configure as below
    1. Enter URL as below. Note that we have use Variable for Token to make URL dynamic.To read from different module change URL (e.g. rather than Leads in URL use Accounts).  Click Here to get full list of Modules and Fields
      You can also use lastModifiedTime parameter along leads modified after certain date/time (this time is Local time and not the UTC). To extract leads modified after certain date (Create a datetime variable called varExtractDate) and use it as below in the URL. You can use any valid format specifiers for date time
    2. Click on the Select Filter and select node with Array icon as below. Each Module may have different structure for example for Leads module Filter may look like $.response.result.Leads.row[*]
      Configure SSIS JSON Source - Read Zoho CRM Leads (getRecords API call with lastModifiedDate Example)

      Configure SSIS JSON Source – Read Zoho CRM Leads (getRecords API call with lastModifiedDate Example)

    3. Now go to 2D Array Tab and configure like below
      1. Select Transform Type to Key/Value Pivot Mode.
      2. Select or type Column Filter as $.FL[*].val
      3. Select or type Row Value Filter as $.FL[*].content
    4. Once you configure 2D Array Transform click Preview to see sample data
    5. .
    6. Click OK to save JSON Source and attach it to target such as SQL Server Destination like below.
    7. Execute Package to test.
      SSIS Example : Loading Zoho CRM data to SQL Server Table (JSON API Source)

      SSIS Example : Loading Zoho CRM data to SQL Server Table (JSON API Source)

Write data to Zoho using SSIS (Import SQL Server Table to Zoho CRM)

Now you know how to read data from Zoho CRM using JSON API Source next step is to load data to Zoho CRM. For writing data we will use following three components

For this example we will use hard coded data in CSV Source (Sample Leads ) but in real world you will have data coming from some RDBMS such as SQL Server.

  1. Drag Data flow Task and double click to go to Data flow designer.
  2. Drag ZS CSV Source from the SSIS Toolbox. Change Access mode to Direct Value. Enter following sample data for demo.
  3. Now drag ZS Template Transform. Connect it with previous step.
  4. Double click Template Transform to configure it with following sample Text (This transform was introduced in v2.6.6. If you dont see it then update your SSIS PowerPack to latest version). Use of XMLENC is optional for Non string columns or Alphanumeric values but if you not sure about column type then just add it anyways.
  5. Now drag another ZS Template Transform and connect it with previous step.
  6. Double click Template Transform to configure. Enter below text. This is final text we will use as Request Body in the Web API Destination.
  7. Drag Microsoft Derived Column Transform. Connect with previous step and double click it to configure.
  8. Select Replace ‘TemplateOutput’ action. Type new column name in the first column e.g. TemplateOutput_Encoded
  9. Now drag ZS Web API Destination, Connect with previous step and double click to configure as below.
    1. Select New HTTP Connection. Enter some valid URL e.g. https://zoho.com  (This will be ignored anyways because we will use Dynamic URL on main UI)
    2. Enter URL as below (see how we again added API token as dynamic variable) . If you inserting to different Module (e.g. Accounts rather than Leads then change accordingly).   Click Here to get full list of Modules and Fields
    3. Select Input Body column as TemplateOutput_Encoded
    4. Enter Sample Body as below if you like to use Test feature else ignore this step. Click Test to see its working. Capture Response XML because you may need in the next step to feed as sample to XML Parser Transform (again optional step).
  10. Now once API destination is set we can optionally configure below steps if you want to store status and ID of newly created records. If you don’t care for that then skip below steps.
  11. Drag ZS XML Parser Transform, Connect it Web API Destination (Response Output – Blue Arrow). Double click XML Parser to configure as below
    1. Select Input XMl Column as ResponseText
    2. Enter Sample Text as below
       
    3. Enter Filter as below
    4. Now Go to 2D Array Transform Tab. Configure as below
    5. Select Transform Mode to Key/Value
    6. Column name filter as   $.FL[:3].@val
    7. Row value filter as   $.FL[:3].#text
    8. Now click OK to close.
  12. Drag ZS Trash Destination or some other destination (e.g. SQL Server) and connect Red arrow coming from Web API destination to this component to capture any errors.
  13. Thats it now you ready to run your SSIS sample package which Inserts Leads to Zoho CRM. See below for full package.
SSIS Example: Import data to Zoho CRM using SSIS Web API Destination (Loading Leads, Accounts, Contacts)

SSIS Example: Import data to Zoho CRM using SSIS Web API Destination (Loading Leads, Accounts, Contacts)

Truncation related error

The most common error you may face when you run an SSIS package is truncation error. During the design time only 300 rows are scanned from a source (a file or a REST API call response) to detect datatypes but at runtime, it is likely you will retrieve far more records. So it is possible that you will get longer strings than initially expected. For detailed instructions on how to fix common metadata related errors read an article "How to handle SSIS errors (truncation, metadata issues)".

Authentication related error

Another frequent error you may get is an authentication error, which happens when you deploy/copy a package to another machine and run it there. Check the paragraph below to see why it happens and how to solve this problem.

Things have gone bad: Error handling & debugging

Incidentally, bad things can happen. A remote server may go offline or your server may go out of memory. In any case, you may want to know when that happens and take actions accordingly. For that purpose, you have to redirect bad rows to some other destination. For this example, we will take and use Web API Destination, but basically, you can use any SSIS component:

Handling errors

  1. Add a Derived Column above Web API Destination with expression "(DT_WSTR,4000)ZS_JSON_OUT" and name it "JsonAsString". This will let you see what JSON you are actually passing.
  2. Then add a database or file destination or use another Trash Destination for debugging purposes and redirect the bad rows (red arrow) from Web API Destination into it. Don't forget to set Redirect row option for both, Error and Truncation columns:
    Redirect bad rows from <em>Web API Destination</em> to <em>Trash Destination</em> when load from SQL Server to Elasticsearch is failing. Add derived column JsonAsString to be able to read JSON you are using.

    Redirected failed requests from Web API Destination to a desired destination when loading from SQL Server to REST API Service is failing. Derived Column JsonAsString added to be able to read JSON which was passed to Elasticsearch

  3. Finally, add a Data Viewer for the red path, if you want to debug the flow. You will be able to see URL, JSON and the error message for each record. You may want to copy-paste ErrorMessage to Notepad if you want it to be more readable:
    Use Data Viewer to view HTTP requests that failed to be fulfilled in Elasticsearch

    Use Data Viewer to view HTTP requests that failed to be fulfilled.

NOTE: You can read more about redirecting rows in SSIS Error Handling (Redirect bad rows) article.

Debugging HTTP requests

A common thing you have to do when working with HTTP requests is to debug those requests; e.g. to check what headers, body or URL was passed. To test how things look behind the scenes we strongly suggest to use Fiddler - a popular web debugging tool.

Inside it, you can double-click the URL entry (Right side) to see Request and Response Panels. The top panel is Request (URL, Headers, Body) and Bottom Panel is Response. For https:// (secure URL) make sure you enable HTTPS option in Fiddler (Tools > Options > HTTPS > Check Decrypt https request):

Debugging Web API call using Fiddler in SSIS

Debugging Web API call using Fiddler in SSIS

Sample SSIS Package Download

Click here to download sample package (SSIS 2012 or Higher).

Conclusion

Zoho CRM API provides great way to automate data read/write operations. However to call Zoho API  you have to use SDK / coding approach (e.g. C#, Java, Python, Ruby). Luckily ZappySys SSIS PowerPack provides great way to integrate any Zoho API call via simple drag and drop approach without coding. Try  SSIS PowerPack for free and call virtually any REST API in few clicks.

 

Posted in REST API Integration, SSIS JSON Source (File/REST), SSIS Template Transform, SSIS WEB API Destination, SSIS XML Parser Transform and tagged , , , , , .