Read/Write ManageEngine ServiceDesk Data in SSIS (Zoho API)

Introduction

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

We will look at step-by-step instructions to read ServiceDesk  data (e.g. Request, Problems, Contacts) and load into SQL Server Database Table.

Authentication ServiceDesk REST API in SSIS (Use OAuth 2.0)

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.

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 ServiceDesk API Connection in SSIS

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.

1. Right click on Connection Panel and choose Choose “New Connection..”. Select ZS-OAUTH type
   

   Create new SSIS OAuth API Connection Manager

2. Configure your OAuth connection General Tab as below
3. OAuth Provider: Custom
4. OAuth Version: OAuth2
5. Client Id , Client Secret
6. Enter One Scope Per Line (For more info on scope read here) . For write you can change READ to WRITE or use ALL as per the help link.
   
   SDPOnDemand.requests.READ

   1	SDPOnDemand.requests.READ

7. Auth URL : https://accounts.zoho.com/oauth/v2/auth
8. Token URL:  https://accounts.zoho.com/oauth/v2/token
9. Configure Advanced tab as below
10. Enter Callback URL : https://zappysys.com/oauth
11. 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)

    

12. Now go back to general tab and Click Generate Token. Close the Popup and use Full browser instead if you see prompt.
13. Click OK to save the connection UI

Read data from ServiceDesk API in SSIS JSON Source

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.

Now lets look at step by step example of reading ManageEngine ServiceDesk data using SSIS (e.g. Requests).

ManageEngine provides JSON API for read action so we will Use SSIS JSON Source for this purpose.

Configure JSON Source

So below steps shows example of reading from ManageEngine Requests API

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://sdpondemand.manageengine.com/api/v3/requests

   1	https://sdpondemand.manageengine.com/api/v3/requests

5. Check Use Credentials. Select OAuth connection manager we created in the previous section.
6. Add the Accept header in HTTP Headers.
   
   Accept: application/vnd.manageengine.sdp.v3+json

   1	Accept: application/vnd.manageengine.sdp.v3+json

7. Under filter options tab, enter Array Filter as   $.requests[*]
8. Now click Preview to see your data
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.

Read ManageEngine ServiceDesk Plus data in SSIS (Cloud Version) – Zoho API

ServiceDesk API Pagination (Read all Requests)

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

1. Double click JSON Source
2. Change URL to include two things (Page size (how many rows per page and start row). Note <%page%> must match with Page Num Indicator you going enter in the next screen
   
   https://sdpondemand.manageengine.com/api/v3/requests?input_data={"list_info":{"row_count":100,"start_index":<%page%>}}

   1	https://sdpondemand.manageengine.com/api/v3/requests?input_data={"list_info":{"row_count":100,"start_index":<%page%>}}

3. Go to Pagination Tab and select URL Path Mode
4. For Page Num Indicator : Enter <%page%>
5. For Increment By : Enter 100
6. Page Start Number select New variable call it StartIndex and start default value 1

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

How to paginate ManageEngine ServiceDesk API

Loading ServiceDesk data into SQL Server Table / Other Target

Write data to ServiceDesk 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 a 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 ServiceDesk. Basically screnshot below is just an example using some other Zoho API but you get the idea.

	Web API Destination
	JSON Generator Transform
	JSON Parser Transform

Create JSON for POST request (Single dataset pattern)

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

Write data to ServiceDesk using SSIS (Import SQL Server Table to ServiceDesk)

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

	Web API Destination
	Template Transform
	XML Parser Transform

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:
   

   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.

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

Conclusion

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