How to read Zendesk data in SSIS with REST API

Introduction

Zendesk is one the most popular customer service platforms available in the market today. Zendesk offers REST API so you can interact with their cloud based service programmatically (manage or read data). The problem is not every one is programmer and not everyone has time to learn how to write C# or JAVA code to access REST API.

In this article you will learn how to get Zendesk data using SSIS JSON Source (REST API Connector). This connector allows you to Read Zendesk data in SSIS using REST API Calls without any scripting/programming. Techniques mentioned in this article can be also used to consume data from other services (e.g. read data from twitter). In this article you will see step by step instructions to read zendesk ticket information and load into on-premises SQL Server.

Also check our another article regarding How to Call Zendesk REST API to create new ticket

Different methods for Zendesk REST API Access

Zendesk allows you to access three different ways so you can access their data.

1. REST API access using OAuth Access Token
2. REST API access using API Token
3. REST API access using Basic Authentication (Use userid (e.g. email) and password)

Each method has pros and cons. Simplest method is third method which is easy to use but requires to pass your account password along with each call. Second method is also easy to use but requires additional step to create access token. First method is most secure but it has few extra steps compared to other methods.

Prerequisite

Before you can finish tasks explained in this article you have to finish below steps. This article assumes you have basic knowledge of SSIS (SQL Server Integration Services)

1. Make sure you have SSIS designer installed. Sometimes its referred as BIDS or SSDT (Get from here)
2. Download SSIS PowerPack (FREE Trial – 30 days).
3. Optional – Another very useful Free tool is Fiddler. You can use it to see raw request/response (Check this article)

Method-1: Zendesk API Access using OAuth in SSIS

To read about OAuth Method click here

Method-2: Zendesk API Access using Token in SSIS

Now let’s look at seconds method to access Zendesk data using SSIS.

Create Token in Zendesk Portal for REST API Access

1. Login to your account https://{{your-subdomain}}.zendesk.com/login and then click on setting icon
2. On the next page click on API link
3. On API page scroll to API Access Tokens section. Make sure Token Access method is checked.
4. Click on add new token hyper link. once prompted give some name to your token (this is just label)
5. Once above steps done token should be created and listed under your API Access page. This token will be used as your password to access REST API (Will show in the next section)

Create new Zendesk REST API Access Token

For more information check this link >>>  How to generate token for ZenDesk API Call

Configure SSIS HTTP Connection

Perform the following steps to extract data from zendesk and load into SQL Server. Below steps assume that you are using API Token method (#2 from supported methods to call ZenDesk API). This is better than using Userid and Password.

1. Open new SSIS Project
2. First of All, Drag and drop Data Flow Task from SSIS Toolbox and double click it to edit.
   

   Dragging and dropping Data Flow Task into Control Flow

3. From the SSIS toolbox drag and drop ZS JSON Source on the data flow designer surface and double click on it to edit.
   

   Drag and Drop JSON Source Component

4. From AccessMode drop down select [Url From Connection]
5. Assume you want to extract all tickets from zendesk then enter following URL (in screenshot we appened ?page=1 which is optional)
   
   https://YOUR_SUB_DOMAIN.zendesk.com/api/v2/tickets.json

   1	https://YOUR_SUB_DOMAIN.zendesk.com/api/v2/tickets.json

6. Check Use Credentials option and select New ZS-HTTP connection. When prompted
   For Credential Type : select Basic Authentication  (Use of UserID and Token ).
   For Web URL : Enter any URL (This will be ignored anyway because URL entered on JSON Source UI will be used)
   For user name : enter YOUR_EMAIL/token  (e.g. bob@mycompany.com/token)
   For Password : enter token obtained from Admin console > API section. For more information check this link >>>  How to generate token for ZenDesk API Call
   
   NOTE: Appending /token command in Userid will allow us to pass token rather than password of that account . This approach is more secure because you can disable token anytime without disabling user account. If you want to access data using your userid and password rather than token then do not use /token after your email. By default use of Token and use of Password is not allowed unless you enable these methods from Admin console (See above section)
   

   Configure SSIS HTTP Connection – Use Zendesk Token to Call REST API

Adding Retry Settings for HTTP or OAuth connection

Zendesk enforces API rate limit which means you cannot issue too many API calls in a given time frame. Check this document for exact information.

ZappySys provides API retry settings on both OAuth Connection and HTTP Connection. For Zendesk you can retry on a specific status code 429

1. Open connection manager UI
2. Go to Retry-Settings page
3. Select Retry web error on matching response status code.
4. Enter 429 for status code
5. Check multiply wait time
6. In the wait time enter 5000 (wait 5 seconds or multiple of 5 seconds)
7. Set Max retry count as 6

See below example (Its not for Zendesk but will give you an idea)

Retry Options

Configure Zendesk API Pagination and Filter for SSIS JSON Source

1. Now click [Select Filter] and select tickets node (see array icon) and click ok. It will generate following Filter Expression to extract all records found under tickets array node (e.g.  tickets : [ {…} , {…} … ] )
   
   $.tickets[*]

   1	$.tickets[*]

   

   Select Filter – Extract Data from Zendesk API Response (Read Tickets)

2. Now lets configure pagination. By default Zendesk API doesn’t return all records so you have to specify pagination as below. Click on Pagination Tab. Enter $.next_page for Next URL attribute as below (you can also Browse Path)
   

   SSIS JSON Source –
   Configure Zendesk REST API Pagination

3. Now time to click Preview 🙂 .. If you followed everything correctly then when you click Preview Button you should see some data in the preview (assuming you have atleast one ticket).
   

   SSIS JSON Source – Data Preview – Zendesk REST API Call to extract tickets

4. Now click on Columns tab to review data types (You can change default length and check Lock option – Last column Right side). This change is needed because datatype guess happens based on designtime setting (only 300 rows scanned by default). At runtime if you get truncation related error then copy error message in notepad to get exact steps and column name caused the error. If you need Length more than 4000 chars then use DT_NTEXT data type instead (Change datatype and click on column name cell right side to save change).
5. Click OK to save UI

How to extract Zendesk data using Incremental API

There will be a time you need to extract data incrementally (i.e. Extract only changed data since the last load). Zendesk API supports many Incremental API endpoints. If you using this endpoint you have to make sure few things.

• For OAuth connection method make sure read  scope is included ( tickets:read  wont work so just use read )
• Your URL must include /incremental path before API type also you must include start_time for extract in Unix Epoch Format.  Following example will pull records modified after
  
  https://zappysys.zendesk.com/api/v2/incremental/tickets.json?start_time=1514764800

  1	https://zappysys.zendesk.com/api/v2/incremental/tickets.json?start_time=1514764800

• On the pagination tab you have to configure following settings
  • Set Next Link as $.next_link
  • Set Stop Indicator attribute as $.count
  • Set Stop Indicator Value as  regex=^\d{1,3}$  . Notice that using regex= prefix treats your end indicator value as pattern check.—OR—
  • Set Stop Indicator attribute as $.end_of_stream   and Stop indicator value as true

Pagination for Zendesk Incremental API – Next Link and Last Page detection using Stop Indicator Regular Expression

You can use ExecuteSQL Task or Script Task to convert datetime to Unix Epoch.

Here is simple SQ!L query to convert date/time to Unix time

For C# code in SSIS Script Task you can use something like below

Assuming you extracted Date/Time from database and saved to some SSIS Variable called User::varLastExtract. Now we like to convert that to Unix format using C#.

We assume you have created following variables in SSIS Package. Also you have set varLastExtract  with some date time value you like to convert.

Use below steps. to convert date time to Unix format in C#

1. Drag and drop Script Task in SSIS designer
2. Add both variable names in Read/Write Variable List (e.g. User::varLastExtractTimeUnix,User::varLastTimeExtract ).
3. Edit Script
4. Enter something like below
   
   DateTime dtUtc= Convert.ToDateTime( Dts.Variables["User::varLastExtractTime"].Value ).ToUniversalTime(); Dts.Variables["User::varLastExtractTimeUnix"].Value = (Int32)(dtUtc.Subtract(new DateTime(1970, 1, 1))).TotalSeconds;

   1 2	DateTime dtUtc= Convert.ToDateTime( Dts.Variables["User::varLastExtractTime"].Value ).ToUniversalTime(); Dts.Variables["User::varLastExtractTimeUnix"].Value = (Int32)(dtUtc.Subtract(new DateTime(1970, 1, 1))).TotalSeconds;

Now you can use this variable in your URL on JSON Source to make things dynamic

Handling duplicate data during incremental load (DISTINCT)

As per this zendesk document they may include last row of previous page into next page if update timestamp. So possible to have duplicate rows in your staging table. You can easily avoid this by using DISTINCT keyword or other duplicate removal SQL query. Make sure not to include P_next_page and P_count because that can be different when duplicate rows detected on page boundaries.

Loading data from Zendesk to SQL Server using SSIS

1. Now drag new OLEDB Destination in the dataflow designer. Connect JSON source with Destination. Now double click Destination. On destination select connection manager (or create one) and once that’s selected click on [New] next to Table name dropdown. You will see default metadata for table based on upstream columns. Notice how P_previous_link is 40 chars rather than 140 this can be edited in source metadata under Columns tab as described in previous step… that way Table datatypes are set correctly. Once you change Table name and datatypes click OK to create new table. Click on Mapping Tab to map source columns to target columns. Once that’s done click OK to save.
   

   Load Zendesk Data into SQL Server Using SSIS JSON Connector (REST API)

2. Now execute package. If you have multiple pages in response you will notice several requests in the log (e.g. &page=1 … &page=2 …).
   
   NOTE: In below screenshot we used per_page=2 for example purpose only. In reality make it bigger e.g. per_page=100 or per_page=200 so you don’t hit server too often. if you not sure then exclude per_page parameter from URL to use default setting.
   

   Execute SSIS Package – Extract data from Zendesk and Load into SQL Server

Method-3: Zendesk API Access using Username and Password in SSIS (Basic Authentication)

This method is same as second method outlined in previous section. Except few changes. When you create HTTP Connection use your Use name (without appending /token ). For password use your account password (same one you use to login to Zendesk portal)

Zendesk API Rate Limit (Throttling)

Zendesk may limit how many calls you can make per second so make sure to slow down if you get errors about api rate limit. You can slow down by entering Wait time on Throttling tab of JSON Source (100 means wait 100 ms after each Request … only useful if pagination is enabled)

Making things Dynamic (e.g. URL)

You can use SSIS variables in your URL to make it dynamic. See below

Dealing with common errors

For detailed instruction to fix common metadata related errors read this article.

SSIS PowerPack – Column Metadata Editor (i.e. Change DataType, Column Length)

Deploy to production

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

In this post you saw how easy it is to read data from Zendesk using SSIS JSON Source (REST API Connector). Click here to Download and try it yourself.

Keywords

Loading Zendesk data into SQL Server
How to read Zendesk tickets in SSIS
Calling Zendesk REST API in SSIS
Fetch Zendesk data using REST API call in SSIS
Extract Zendesk data using REST API call in SSIS
Reading data from zendesk using rest api call
Pulling zendesk tickets using rest api call