{"id":9624,"date":"2022-04-28T18:38:33","date_gmt":"2022-04-28T18:38:33","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=9624"},"modified":"2025-03-19T12:56:14","modified_gmt":"2025-03-19T12:56:14","slug":"read-write-manageengine-servicedesk-data-ssis-zoho-api","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-write-manageengine-servicedesk-data-ssis-zoho-api\/","title":{"rendered":"Read\/Write ManageEngine ServiceDesk Data in SSIS (Zoho API)"},"content":{"rendered":"

Introduction<\/h2>\n
UPDATE:<\/strong>\u00a0ZappySys has released a brand new API Connector for Zoho \/ ManageEngine ServiceDesk Pro OnDemand (Cloud Version)<\/a>\u00a0which makes it much simpler to\u00a0Read\/Write ServiceDesk 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

In this post, you will learn how to read \/ write ManageEngine ServiceDesk data in SSIS (Drag and drop approach without any coding).\u00a0 We will use\u00a0SSIS JSON\/ REST API Source<\/a>\u00a0to extract data from Servicedesk 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 ServiceDesk\u00a0 data (e.g. Request, Problems, Contacts) and load into SQL Server Database Table.<\/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 ServiceDesk REST API in SSIS (Use OAuth 2.0)<\/h2>\n

    Very first step to call ServiceDesk API is you need to register OAuth App in Zoho Portal. Don’t get confused if you see Zoho Portal for ServiceDesk a Product from ManageEngine is in fact Zoho subsidiary (in other words Zoho is the parent company). ServiceDesk has On-Premises version too but this article is only about Cloud version.<\/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 ServiceDesk API Connection in SSIS<\/h3>\n

    Once you create Zoho OAuth App now its time to call some Zoho API. Let’s create the OAuth Connection first. In the next section we will see how to load data in SQL Server.<\/p>\n

      \n
    1. Right click on Connection Panel and choose Choose “New Connection..”. Select ZS-OAUTH<\/strong> type\n
    2. Configure your OAuth connection General Tab as below<\/li>\n
    3. OAuth Provider: Custom<\/strong><\/li>\n
    4. OAuth Version: OAuth2<\/strong><\/li>\n
    5. Client Id<\/strong> , Client Secret <\/strong><\/li>\n
    6. Enter One Scope Per Line (For more info on scope read here<\/a>) . For write you can change READ to WRITE or use ALL as per the help link.\n
      SDPOnDemand.requests.READ<\/pre>\n<\/li>\n
    7. Auth URL :\u00a0https:\/\/accounts.zoho.com\/oauth\/v2\/auth<\/strong><\/li>\n
    8. Token URL:\u00a0\u00a0https:\/\/accounts.zoho.com\/oauth\/v2\/token<\/strong><\/strong>\"\"<\/a><\/li>\n
    9. Configure Advanced tab as below<\/li>\n
    10. Enter Callback URL :\u00a0https:\/\/zappysys.com\/oauth<\/strong><\/li>\n
    11. 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
    12. Now go back to general tab and Click Generate Token<\/strong>. Close the Popup and use Full browser instead if you see prompt.<\/li>\n
    13. Click OK to save the connection UI<\/li>\n<\/ol>\n
      Read data from ServiceDesk API in SSIS JSON Source<\/h2>\n
      In this section we will learn how to read data from ServiceDesk 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 ManageEngine ServiceDesk data using SSIS (e.g. Requests).<\/p>\n
      ManageEngine provides JSON API for read action so we will Use SSIS JSON Source<\/a> for this purpose.<\/p>\n
      Configure JSON Source<\/h3>\n
      So below steps shows example of reading from ManageEngine Requests API<\/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:\/\/sdpondemand.manageengine.com\/api\/v3\/requests<\/pre>\n<\/li>\n
      5. Check Use Credentials. Select OAuth connection manager we created in the previous section.<\/li>\n
      6. Add the Accept header in HTTP Headers.\n
        Accept: application\/vnd.manageengine.sdp.v3+json<\/pre>\n<\/li>\n
      7. Under filter options tab, enter Array Filter<\/strong> as\u00a0\u00a0$.requests[*]<\/span><\/li>\n
      8. Now click Preview to see your data<\/li>\n
      9. Click OK to save UI. In the next section, we will see how to setup pagination and incremental extract of Servicedesk records. After that we will see how to load data into SQL Server Table.<\/li>\n<\/ol>\n
        \"Read<\/a>
        Read ManageEngine ServiceDesk Plus data in SSIS (Cloud Version) – Zoho API<\/p><\/div>\n
        ServiceDesk API Pagination (Read all Requests)<\/h3>\n
        Just like most API ServiceDesk also uses pagination. See below to learn how to configure pagination in JSON Source for ServiceDesk API.<\/p>\n
          \n
        1. Double click JSON Source<\/li>\n
        2. Change URL to include two things (Page size (how many rows per page and start row). Note <%page%> must match with\u00a0Page Num Indicator <\/strong>you going enter in the next screen\n
          https:\/\/sdpondemand.manageengine.com\/api\/v3\/requests?input_data={\"list_info\":{\"row_count\":100,\"start_index\":<%page%>}}<\/pre>\n<\/li>\n
        3. Go to Pagination<\/strong> Tab and select URL Path Mode<\/strong><\/li>\n
        4. For Page Num Indicator<\/strong>\u00a0: Enter <%page%><\/span><\/li>\n
        5. For Increment By<\/strong>\u00a0: Enter 100<\/span><\/li>\n
        6. Page Start Number select New variable call it StartIndex and start default value 1<\/strong><\/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
          \"How<\/a>
          How to paginate ManageEngine ServiceDesk API<\/p><\/div>\n
          Loading ServiceDesk 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