{"id":2625,"date":"2018-02-16T04:21:06","date_gmt":"2018-02-16T04:21:06","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=2625"},"modified":"2025-12-11T11:21:18","modified_gmt":"2025-12-11T11:21:18","slug":"read-write-zoho-crm-data-using-ssis-rest-api-call","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-write-zoho-crm-data-using-ssis-rest-api-call\/","title":{"rendered":"Read \/ Write Zoho CRM data using SSIS REST API Call"},"content":{"rendered":"

Introduction<\/h2>\n
UPDATE:<\/strong>\u00a0ZappySys has released a brand new API Connector for Zoho CRM Online<\/a> which makes it much simpler to\u00a0Read\/Write Zoho CRM Data in SSIS<\/strong> 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.<\/p>\n

Please visit this page to see all<\/a>\u00a0Pre-Configured ready to use API connectors which you can use in SSIS API Source<\/a> \/ SSIS API Destination<\/a> OR\u00a0API ODBC Driver<\/a> (for non-SSIS Apps such as Excel, Power BI, Informatica).<\/p>\n<\/div><\/div>\n\"\"<\/a>In this post, you will learn how to read \/ write Zoho CRM data using SSIS (Drag and drop approach without any coding).\u00a0 We will use\u00a0SSIS JSON\/ REST API Source<\/a>\u00a0to extract data from Zoho API and use\u00a0SSIS Web API Destination<\/a>\u00a0to write data to Zoho.<\/p>\n

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.<\/p>\n

Now let\u2019s look at step-by-step approach to call Zoho API\u00a0 using SSIS.<\/p>\n

 <\/p>\n

Prerequisites<\/span><\/h2>\r\nBefore we perform the steps listed in this article, you will need to make sure the following prerequisites are met:\r\n
    \r\n \t
  1. SSIS<\/abbr> designer installed.\u00a0Sometimes it is referred to as BIDS<\/abbr> or SSDT<\/abbr> (download it from the Microsoft site<\/a>).<\/li>\r\n \t
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\r\n \t
  3. Make sure\u00a0ZappySys SSIS PowerPack<\/a><\/span>\u00a0is installed (download it<\/a>, if you haven't already).<\/li>\r\n \t
  4. (Optional step<\/em>).<\/em>\u00a0Read this article<\/a>, if you are planning to deploy packages to a server and schedule their execution later.<\/li>\r\n<\/ol><\/div>\n

    Authentication Using Zoho V2 REST API (OAuth 2.0)<\/h2>\n

    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.<\/p>\n

    Register Zoho OAuth App and get ClientID\/ Secret (*** MUST READ ***)<\/h3>\n

    Check this article<\/a> to register your OAuth App and obtain Client ID\u00a0 \/Client Secret<\/p>\n

    Create Zoho OAuth Connection in SSIS (for API call)<\/h3>\n

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

      \n
    1. Open SSIS Package and go to SSIS Designer (Control Flow)<\/li>\n
    2. Drag ZS REST API Task<\/strong> from Control Flow SSIS Toolbox as below\n
      \"Drag

      Drag and drop SSIS REST API Task from SSIS Toolbox<\/p><\/div><\/li>\n

    3. Double click the task to configure.<\/li>\n
    4. Change URL Access mode to URL from Connection<\/strong><\/li>\n
    5. Enter API URL<\/strong> you like to call. For example you can enter below\n
      https:\/\/www.zohoapis.com\/crm\/v2\/settings\/modules<\/pre>\n<\/li>\n
    6. Select New ZS-OAUTH<\/strong> connection from Connection drop down\n
    7. Configure your OAuth connection General Tab as below\n
        \n
      1. OAuth Provider: Custom<\/strong><\/li>\n
      2. OAuth Version: OAuth2<\/strong><\/li>\n
      3. Client Id<\/strong> , Client Secret <\/strong><\/li>\n
      4. Enter One Scope Per Line (For more info on scope read here<\/a>)\n
        ZohoCRM.modules.READ\r\nZohoCRM.settings.READ<\/pre>\n<\/li>\n
      5. Auth URL :\u00a0https:\/\/accounts.zoho.com\/oauth\/v2\/auth<\/strong><\/li>\n
      6. Token URL:\u00a0\u00a0https:\/\/accounts.zoho.com\/oauth\/v2\/token<\/strong><\/strong>\"\"<\/a><\/li>\n<\/ol>\n<\/li>\n
      7. Configure Advanced tab as below\n
          \n
        1. Enter Callback URL :\u00a0https:\/\/zappysys.com\/oauth<\/strong><\/li>\n
        2. Enter Extra Attribute for \/Auth :\u00a0access_type=offline&prompt=consent<\/strong>
          \nThis is needed to obtain refresh_token (if you ommit this then only access_token returned which cannot be renewed once expired after 1hr)<\/p>\n
          <\/div>\n
          \"\"<\/a><\/li>\n<\/ol>\n<\/li>\n
        3. Now go back to general tab and Click Generate Token<\/strong>.\n
        4. Click OK to save the connection UI<\/li>\n<\/ol>\n
          Call Zoho API using OAuth Connection<\/h3>\n
            \n
          1. On the REST API Task click Test Request \/ Response\n
            \"SSIS<\/a>
            SSIS REST API Task – Call Zoho REST API in SSIS (OAuth 2.0)<\/p><\/div><\/li>\n<\/ol>\n
            Read data from Zoho Table in SSIS JSON Source<\/h2>\n
            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.<\/p>\n
            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<\/a> for this purpose. Full sample package is attached at the end of this article but see below steps for high level configuration.<\/p>\n
            Configure JSON Source<\/h3>\n
            So below steps shows example of reading from Zoho Lead modules using v2 records API Call<\/a>.<\/p>\n
              \n
            1. Open SSIS Package<\/li>\n
            2. Drag data flow task from Control flow SSIS Toolbox\n
              \"Drag
              Drag data flow from SSIS Toolbox<\/p><\/div><\/li>\n
            3. Go to data flow and drag ZS JSON Source<\/a> from SSIS Toolbox\n
              \"Drag
              Drag and drop SSIS JSON Source (REST API or File)<\/p><\/div><\/li>\n
            4. Double click JSON Source to configure.\u00a0Enter URL as below. You can supply few more parameters.\u00a0Click Here to get full list of parameters and usage<\/a>. 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.\n
              https:\/\/www.zohoapis.com\/crm\/v2\/Leads?per_page=200<\/pre>\n<\/li>\n
            5. Check Use Credentials. Select OAuth connection manager we created in the previous section.<\/li>\n
            6. Under filter options tab, enter Array Filter as\u00a0\u00a0$.data[*]<\/span><\/li>\n
            7. Now click Preview to see your data\n
            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.<\/li>\n<\/ol>\n
              Zoho API Pagination (Read all rows)<\/h3>\n
              Just like most API zoho also uses pagination. See below for how to configure pagination in JSON Source for Zoho API.<\/p>\n
                \n
              1. Double click JSON Source<\/li>\n
              2. Go to the\u00a0Pagination<\/strong> Tab and select URL Parameter Mode<\/strong><\/li>\n
              3. For Page Num Indicator<\/strong> Enter page<\/span><\/li>\n
              4. For Last Page Detection<\/strong> select Detect last page based on status code<\/span><\/li>\n
              5. In the Status code field
                \nenter 304<\/strong> (if you added\u00a0If-Modified-Since header for incremental extract – See next section)
                \nenter 204<\/strong> (if you do not add If-Modified-Since)<\/li>\n<\/ol>\n
                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<\/a><\/p>\n
                \"Paginate<\/a>
                Paginate Zoho API records<\/p><\/div>\n
                Incremental Extract based on Last Modified Date\/Time field<\/h3>\n
                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<\/span>\u00a0\u00a0HTTP header along with your request. Here is how to do.<\/p>\n
                  \n
                1. On the JOSN Source, click Raw Edit above HTTP Headers Grid.\u00a0 We can define Header to extract data after last modified date. Skip this step if you like to extract all records.
                  \nFor 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.<\/p>\n
                  If-Modified-Since: 2019-12-31T00:00:00<\/pre>\n
                  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.<\/p>\n
                  If-Modified-Since: <<{{User::varExtractDate,yyyy-MM-ddTHH:mm:ss}},FUN_TO_UTC_DATE>><\/pre>\n<\/li>\n<\/ol>\n
                  <\/h2>\n
                  Loading Zoho data into SQL Server Table \/ Other Target<\/h3>\n
                  ZappySys SSIS PowerPack makes it easy to load data from various sources such as REST, SOAP, JSON, XML, CSV or from other source into SQL Server, or PostgreSQL, or Amazon Redshift, or other  targets. The Upsert Destination<\/strong> component allows you to automatically insert new records and update existing ones based on key columns. Below are the detailed steps to configure it.<\/p>\r\n
                  Step 1: Add Upsert Destination to Data Flow<\/h3>\r\n
                    \r\n
                  1. Drag and drop the Upsert Destination<\/strong> component from the SSIS Toolbox.<\/li>\r\n
                  2. Connect your source component (e.g., JSON \/ REST \/ Other Source) to the Upsert Destination.<\/li>\r\n<\/ol>\r\n
                    \r\n\r\n\"\"<\/a>\r\n
                    SSIS - Data Flow - Drang and Drop Upsert Destination Component<\/p>\r\n<\/div>\r\n
                    Step 2: Configure Target Connection<\/h3>\r\n
                      \r\n
                    1. Double-click the Upsert Destination<\/strong> component to open the configuration window.<\/li>\r\n
                    2. Under Connection<\/strong>, select an existing target connection or click NEW<\/strong> to create a new connection.\r\n