Read / Write Zoho CRM data using SSIS REST API Call

Introduction

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.

 

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

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
   
   https://www.zohoapis.com/crm/v2/settings/modules

   1	https://www.zohoapis.com/crm/v2/settings/modules

6. Select New ZS-OAUTH connection from Connection drop down
   

   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)
      
      ZohoCRM.modules.READ ZohoCRM.settings.READ

      1 2	ZohoCRM.modules.READ ZohoCRM.settings.READ

   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

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)

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

3. Go to data flow and drag ZS JSON Source from SSIS Toolbox
   

   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.
   
   https://www.zohoapis.com/crm/v2/Leads?per_page=200

   1	https://www.zohoapis.com/crm/v2/Leads?per_page=200

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)

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

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-Modified-Since: 2019-12-31T00:00:00

   1	If-Modified-Since: 2019-12-31T00:00:00

   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.
   
   If-Modified-Since: <<{{User::varExtractDate,yyyy-MM-ddTHH:mm:ss}},FUN_TO_UTC_DATE>>

   1	If-Modified-Since: <<{{User::varExtractDate,yyyy-MM-ddTHH:mm:ss}},FUN_TO_UTC_DATE>>

Loading Zoho data into SQL Server Table / Other Target

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.

 

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.

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 )
   
   # #Mon May 15 12:21:42 PDT 2017 AUTHTOKEN=8a083f76xxxxxxxxxxxxxxxxxxxdxxxxxxxx13630d RESULT=TRUE

   1 2 3 4

   # #Mon May 15 12:21:42 PDT 2017 AUTHTOKEN=8a083f76xxxxxxxxxxxxxxxxxxxdxxxxxxxx13630d RESULT=TRUE

7. Now use the newly created auth token to your API call.

 

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
      
      https://crm.zoho.com/crm/private/json/Leads/getRecords?newFormat=1&authtoken={{User::token}}&scope=crmapi&fromIndex=0&toIndex=200

      1	https://crm.zoho.com/crm/private/json/Leads/getRecords?newFormat=1&authtoken={{User::token}}&scope=crmapi&fromIndex=0&toIndex=200

      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
      
      https://crm.zoho.com/crm/private/json/Leads/getRecords?newFormat=1&authtoken={{User::token}}&scope=crmapi&fromIndex=0&toIndex=200&lastModifiedTime={{User::varExtractDate,yyyy-MM-dd HH:mm:ss}}

      1	https://crm.zoho.com/crm/private/json/Leads/getRecords?newFormat=1&authtoken={{User::token}}&scope=crmapi&fromIndex=0&toIndex=200&lastModifiedTime={{User::varExtractDate,yyyy-MM-dd HH:mm:ss}}

   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)

   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)

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.
   
   Company,First Name,Last Name,Email,Lead Status,Lead Source,Phone Microsoft,TestMaawia,TestBrito,Maawia@outlook.com,Lunk Lead,Web Download,111-111-3333 Microsoft,Testraja,Testvedaiyan,raja@outlook.com,Lunk Lead,Web Download,111-222-3333 Yahoo,TestJayder,Testelnageib,Jayder@outlook.com,Lunk Lead,Web Download,111-333-3333 Yahoo,TestStephen,TestTjebane,Stephen@outlook.com,Lunk Lead,Web Download,111-444-3333 Google,TestHarold,TestBatista,Harold@gmail.com,Lunk Lead,Web Download,111-555-3333 Google,TestVennon,TestMills,Vennon@gmail.com,Lunk Lead,Web Download,111-666-3333 Google,TestMichael,TestTremblay,Michael@gmail.com,Lunk Lead,Web Download,111-777-3333

   1 2 3 4 5 6 7 8

   Company,First Name,Last Name,Email,Lead Status,Lead Source,Phone Microsoft,TestMaawia,TestBrito,Maawia@outlook.com,Lunk Lead,Web Download,111-111-3333 Microsoft,Testraja,Testvedaiyan,raja@outlook.com,Lunk Lead,Web Download,111-222-3333 Yahoo,TestJayder,Testelnageib,Jayder@outlook.com,Lunk Lead,Web Download,111-333-3333 Yahoo,TestStephen,TestTjebane,Stephen@outlook.com,Lunk Lead,Web Download,111-444-3333 Google,TestHarold,TestBatista,Harold@gmail.com,Lunk Lead,Web Download,111-555-3333 Google,TestVennon,TestMills,Vennon@gmail.com,Lunk Lead,Web Download,111-666-3333 Google,TestMichael,TestTremblay,Michael@gmail.com,Lunk Lead,Web Download,111-777-3333

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.
   
   <Leads> <row no="1"> <FL val="Lead Status"><%Lead Status,XMLENC%></FL> <FL val="Company"><%Company,XMLENC%></FL> <FL val="First Name"><%First Name,XMLENC%></FL> <FL val="Last Name"><%Last Name,XMLENC%></FL> <FL val="Email"><%Email,XMLENC%></FL> <FL val="Phone"><%Phone,XMLENC%></FL> <FL val="Lead Source">Web Download</FL> </row> </Leads>

   1 2 3 4 5 6 7 8 9 10 11

   <Leads> <row no="1">   <FL val="Lead Status"><%Lead Status,XMLENC%></FL>   <FL val="Company"><%Company,XMLENC%></FL>   <FL val="First Name"><%First Name,XMLENC%></FL>   <FL val="Last Name"><%Last Name,XMLENC%></FL>   <FL val="Email"><%Email,XMLENC%></FL>   <FL val="Phone"><%Phone,XMLENC%></FL>   <FL val="Lead Source">Web Download</FL> </row> </Leads>

   
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.
   
   xmlData=<%TemplateOutput,URLENC%>

   1	xmlData=<%TemplateOutput,URLENC%>

   
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
      
      https://crm.zoho.com/crm/private/xml/Leads/insertRecords?newFormat=1&authtoken={{User::token}}&scope=crmapi&duplicateCheck

      1	https://crm.zoho.com/crm/private/xml/Leads/insertRecords?newFormat=1&authtoken={{User::token}}&scope=crmapi&duplicateCheck

   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).
      
      <Leads> <row no="1"> <FL val="Lead Status">Junk Lead</FL> <FL val="Company">ABCD Inc</FL> <FL val="First Name">Bob</FL> <FL val="Last Name">Smith</FL> <FL val="Email">bob@abc.com</FL> <FL val="Phone">111-222-3333</FL> <FL val="Lead Source">Web Download</FL> </row> </Leads>

      1 2 3 4 5 6 7 8 9 10 11

      <Leads> <row no="1">   <FL val="Lead Status">Junk Lead</FL>   <FL val="Company">ABCD Inc</FL>   <FL val="First Name">Bob</FL>   <FL val="Last Name">Smith</FL>   <FL val="Email">bob@abc.com</FL>   <FL val="Phone">111-222-3333</FL>   <FL val="Lead Source">Web Download</FL> </row> </Leads>

      
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
       
       <?xml version="1.0" encoding="UTF-8" ?> <response uri="/crm/private/xml/Leads/insertRecords"> <result> <message>Record(s) added successfully</message> <recorddetail> <FL val="Id">1558554000015597002</FL> <FL val="Created Time">2018-02-13 16:14:10</FL> <FL val="Modified Time">2018-02-13 16:14:10</FL> <FL val="Created By"> <![CDATA[ZappySys Support]]> </FL> <FL val="Modified By"> <![CDATA[ZappySys Support]]> </FL> </recorddetail> </result> </response>

       1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

       <?xml version="1.0" encoding="UTF-8" ?> <response uri="/crm/private/xml/Leads/insertRecords"> <result> <message>Record(s) added successfully</message> <recorddetail> <FL val="Id">1558554000015597002</FL> <FL val="Created Time">2018-02-13 16:14:10</FL> <FL val="Modified Time">2018-02-13 16:14:10</FL> <FL val="Created By"> <![CDATA[ZappySys Support]]> </FL> <FL val="Modified By"> <![CDATA[ZappySys Support]]> </FL> </recorddetail> </result> </response>

        
    3. Enter Filter as below
       
       $.response.result.recorddetail[*]

       1	$.response.result.recorddetail[*]

    4. 
    5. Now Go to 2D Array Transform Tab. Configure as below
    6. Select Transform Mode to Key/Value
    7. Column name filter as   $.FL[:3].@val
    8. Row value filter as   $.FL[:3].#text
    9. 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.

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.