- 1 Introduction
- 2 Prerequisites
- 3 Authentication ServiceDesk REST API in SSIS (Use OAuth 2.0)
- 4 Read data from ServiceDesk API in SSIS JSON Source
- 5 Write data to ServiceDesk using SSIS (Import SQL Server Table to ServiceDesk)
- 6 Things have gone bad: Error handling & debugging
- 7 Conclusion
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.
PrerequisitesBefore we perform the steps listed in this article, you will need to make sure the following prerequisites are met:
- SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
- (Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.
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.
- Right click on Connection Panel and choose Choose “New Connection..”. Select ZS-OAUTH type
- Configure your OAuth connection General Tab as below
- OAuth Provider: Custom
- OAuth Version: OAuth2
- Client Id , Client Secret
- 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.
- Auth URL : https://accounts.zoho.com/oauth/v2/auth
- Token URL: https://accounts.zoho.com/oauth/v2/token
- Configure Advanced tab as below
- Enter Callback URL : https://zappysys.com/oauth
- 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)
- Now go back to general tab and Click Generate Token. Close the Popup and use Full browser instead if you see prompt.
- 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
- Open SSIS Package
- Drag data flow task from Control flow SSIS Toolbox
- Go to data flow and drag ZS JSON Source from SSIS Toolbox
- 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.
- Check Use Credentials. Select OAuth connection manager we created in the previous section.
- Add the Accept header in HTTP Headers.
- Under filter options tab, enter Array Filter as $.requests[*]
- Now click Preview to see your data
- 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.
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.
- Double click JSON Source
- 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
- Go to Pagination Tab and select URL Path Mode
- For Page Num Indicator : Enter <%page%>
- For Increment By : Enter 100
- 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
Loading ServiceDesk data into SQL Server Table / Other Target
- Inside Data Flow, Drag and drop Upsert Destination Component from SSIS Toolbox
- Connect our Source component to Upsert Destination
- Double click Upsert Destination to configure it
- Select Target Connection or click NEW to create new connection Configure SSIS Upsert Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS
- Select Target Table or click NEW to create new table based on source columns
- Click on Mappings Tab to Auto map columns by name. You can change mappings as you need SSIS Upsert Destination - Columns Mappings
- Click OK to Save Upsert Destination Settings
- 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
- To execute data flow, Right click anywhere inside Data Flow Surface and click Execute Task
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.
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
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)".
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:
- 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.
- 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:
- 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:
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):
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.