Read Zendesk data in SSIS using 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 Basic Authentication (Use userid (e.g. email) and password)
  2. REST API access using API Token
  3. REST API access using OAuth Access Token

Each method has pros and cons. First method is easy to use but requires to use your account password. Second method is also easy to use but requires additional step to create access token. Third method is most secure but it has several extra steps compared to first and second methods.

In this article we will only show you 2nd method (Using API Access Token).

Create API Access Token

  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

Create new Zendesk REST API Access Token

Configure SSIS JSON to extract Tickets from Zendesk

Perform the following steps to extract data from zendesk and load into SQL Server

  1. Create new ssis variable (name it auth just like in the screenshot)
  2. If you are using Method#2 (e.g. Use API Access Token) then you have to type your email you use to login to zendesk and Token you copied from Zendesk admin console. Format for this methos is YOUR_EMAIL/token:YOUR_ACCESS_TOKEN. If you want to access data using your userid and password then format is YOUR_EMAIL:YOUR_PASSWORD (no /token after email)
  3. Once variable is set with email and token you can drag dataflow task into control flow
  4. Double click dataflow and from toolbox drag ZappySys JSON Source
  5. Double click JSON Source Connector to configure Zendesk REST API Access
  6. On JSON Source UI type service URL you want to access. In our case its extracting ticket information so service URL may look like below (Notice subdomain part can be different in your case. Each zendesk customer has unique subdomainhttps://YOUR_SUB_DOMAIN.zendesk.com/api/v2/tickets.jsonFor more detailed documentation about Zendesk REST API click here
  7. Once REST API URL is set enter Authorization header as per below screenshot. Notice how we have used variable name with BASE64ENC instruction. This will automatically convert value of variable into BASE64 encoding (This is required step to transmit your credentials).
    SSIS JSON Source Connector - Reading Data from Zendesk  using REST API

    SSIS JSON Source Connector – Reading Data from Zendesk using REST API

  8. In the above screenshot you will notice how we entered JSON Path filter to extract tickets (e.g. $.tickets). This will extract all records from Tickets array from Response JSON and convert to multiple records.
  9. Now click on Pagination Tab and configure settings as below
    Configure paging for Zendesk REST API

    Configure paging for Zendesk REST API

  10. Once everything is set you can click Preview button to see your data. Click on Columns tab and adjust any datatype you may think can cause issue. By default system will guess appropriate column length and datatype but sometimes its not correct because datatype guess happens based on designtime setting (only 300 rows scanned by default)
  11. Click OK to save UI
  12. 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)

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

  13. Now execute package. If you have multiple pages in response you will notice several requests in the log (e.g. &page=1 … &page=2 …)
    Execute SSIS Package - Extract data from Zendesk and Load into SQL Server

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

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.

Posted in SSIS JSON Source and tagged , , , , .