Pricing
Log in

BY RUDDERSTACK TEAM

How to Load data from Zendesk to MS SQL Server

01. Extract Data from Zendesk

  • 01. Extract Data from Zendesk
  • 02. Load Data
  • 03. Update Data
  • 04. Extract Data

Try RudderStack for free

Sign up for RudderStack for free.

This post helps you with loading your data from Zendesk to MS SQL Server. If you are looking to get analytics-ready data without the manual hassle you can integrate Zendesk to MS SQL Server with RudderStack, so you can focus on what matters, getting value out of your support data.

Sign Up For Free And Start Sending Data

Test out our event stream, ELT, and reverse-ETL pipelines. Use our HTTP source to send data in less than 5 minutes, or install one of our 12 SDKs in your website or app.

Extract your Zendesk’s data

Zendesk APIs are not specific to pulling data, Zendesk provides more than a hundred different APIs for you to integrate with. So you can easily manage your users, enhance your team’s productivity and create seamless integrations. You can create integrations or even enrich Zendesk with data coming from external sources. Zendesk API is a RESTful API that can be accessed through HTTP. As a RESTful API, interacting with it can be achieved by using tools like CURL or Postman or by using HTTP clients for your favorite language or framework.

A few suggestions:

  • Apache HttpClient for Java
  • Spray-client for Scala
  • Hyper for Rust
  • Ruby rest-client
  • Python http-client

Additionally, Zendesk offers a number of SDKs and libraries so you can access the API from your framework of choice without having to deal with the technicalities of HTTP. API clients are available for the following languages:

  • Ruby
  • Python
  • PHP
  • Java
  • .NET
  • Node.js
  • Clojure
  • Force.com
  • R

Zendesk API Authentication

Zendesk’s API is an SSL-only API, regardless of how your account is configured. You must be a verified user to make API requests. You can authorize against the API using either basic authentication with your email address and password, with your email address and an API token, or with an OAuth access token.

Zendesk Rate Limiting

The API is rate-limited. It only allows a certain number of requests per minute depending on your plan and the endpoint. Zendesk reserves the right to adjust the rate limit for given endpoints to provide a high quality of service for all clients. The current limits are the following:

Pagination

By default, most list endpoints return a maximum of 100 records per page. You can change the number of records on a per-request basis by passing a per_page parameter in the request URL parameters. Example: per_page=50. However, you can’t exceed 100 records per page on most endpoints.

When the response exceeds the per-page maximum, you can paginate through the records by incrementing the page parameter. Example: page=3. List results include next_page and previous_page URLs in the response body for easier navigation:

JAVASCRIPT
{
"users": [ ... ],
"count": 1234,
"next_page": "https://account.zendesk.com/api/v2/users.json?page=2",
"previous_page": null
}

Endpoints and Available Resources

The Zendesk REST API exposes a large number of resources and endpoints that allow the user to interact with the platform in every possible way. Thus it is possible to create new applications on top of the Zendesk platform, integrate external systems with it, and of course, pull data out of the platform. The most important resources are the following:

  1. The tickets that your customers create through Zendesk.
  2. Ticket events. Changes that have occurred to the tickets.
  3. Organizations.
  4. Users.
  5. Ticket metrics. These are metrics related to your tickets.
  6. Data related to the Net Promoter Score.
  7. Articles

Let’s assume that we want to pull all the tickets we have on Zendesk. To do that we need to perform a GET request to the appropriate end-point, like this: GET /api/v2/incremental/tickets.json?start_time=1332034771

SH
curl https://{subdomain}.zendesk.com/api/v2/incremental/tickets.json?start_time=1332034771 \
-v -u {email_address}:{password}

And a sample response:

JSON
Status: 200 OK
{
"end_time": 1383685952,
"next_page": "https://{subdomain}.zendesk.com/api/v2/incremental/tickets.json?start_time=1383685952",
"count": 1,
"tickets": [
{
"url": "https://{subdomain}.zendesk.com/api/v2/tickets/1.json",
"id": 2,
"created_at": "2012-02-02T04:31:29Z",
"generated_timestamp": 1390362285
...
},
...
]
}

A complete ticket object might contain the following fields:

The results of the Zendesk API are always in JSON format. The API offers you the opportunity to get very granular data about your accounting activities and use it for analytics and reporting purposes.

About Zendesk

Zendesk provides a cloud-based customer service platform, that includes ticketing, self-service options, and customer support features. Zendesk focuses on improving the communication between your customers and your company. It brings all your customer communication into one place. The supported communication channels are:

  1. Mail. Zendesk helps to organize all the emails you receive from your customers.
  2. Social. You can connect your Facebook and Twitter account with Zendesk.
  3. Voice. Take customer calls from within Zendesk.
  4. Chat. Zopim Chat allows you to communicate with your customers from within your product.

Zendesk’s help desk software helps streamline customer support with time-saving tools like triggers and automation. And it’s intuitive, built with the experience of customer service and support desk agents in mind. Some important features of the ticketing system that Zendesk offers are:

It helps to Solve tickets better with teammates through a collaborative environment where information can be shared.

Get access to relevant information across teams. Zendesk comes with an internal Knowledge Base that allows agents to quickly refer to information and processes without losing their place.

Make everyone inside your company a support agent. With light agents, everyone inside your company can view tickets and make private comments.

Additionally, Zendesk offers a suite of analytics tools that will help you to get closer to your user through data. With these tools you can:

  • Gain visibility into customers interactions
  • Measure your team’s performance
  • See the business impacts of great service

It is possible to track a large number of metrics related to your customers, support teams, and your business.

But in case that you would like to run some more engaged analysis with your Zendesk data, or fuse the customer support related data with data originated in other sources like your transactional database and logs, Zendesk exposes a rich ecosystem of APIs and tools that you can use to access and pull your data among other functionalities.

Load data from Zendesk to MS SQL Server

As a feature-rich and mature product, MS SQL Server offers a large and diverse set of methods for loading data into a database. One way of importing data into your database is by using the SQL Server Import and Export Wizard. With it and through a visual interface you will be able to bulk load data from a number of data sources that are supported.

You can import data using another SQL Server, from an Oracle database, from Flat Files, from an Access Data Source, PostgreSQL, MySQL, and finally Azure Blob Storage. Especially if you are using a managed version of MS SQL Server on Azure, you should definitely consider utilizing the Azure Blob Storage connection.

In this way, you will be loading data as Blobs on Azure and your MS SQL Server database will sync with it through the Import and Export Wizard.

Another way for importing bulk data into an SQL Server, both on Azure and on-premises, is by using the bcp utility. This is a command-line tool that is built specifically for bulk loading and unloading of data from an MS SQL database.

Finally and for compatibility reasons, especially if you are managing databases from different vendors, you can BULK INSERT SQL statements.

In a similar way and as it happens with the rest of the databases, you can also use the standard INSERT statements, where you will be adding data row-by-row directly to a table. It is the most basic and straightforward way of adding data into a table but it doesn’t scale very well with larger data sets.

So for bulk datasets, you better consider one of the previous methods.

About Microsoft SQL Server

Microsoft SQL Server is one of the oldest and most mature database systems. Its first version was introduced about 28 years ago, in 1989, and Microsoft has been consistently supporting and extending the product until today.

So, it’s no surprise that Microsoft SQL Server has one of the richest feature sets among the currently available database systems.

SQL Server is delivered in different editions or flavors. The most notable being the Enterprise edition which can manage databases as large as 524 petabytes utilizing up to 12 terabytes of memory and 640 CPU processors. A free and scaled-down version is called Express.

A Business Intelligence version focusing on use cases where BI is performed on-premises. This version is actually a bundle of different products, including the core database system, together with other Microsoft-related products that can be used for BI purposes like visualization and data management.

In addition, there are also plenty of specialized versions of the database like the Compact edition that can be used on small devices and of course the Azure version, which is the cloud-based edition of SQL Server. Microsoft SQL Server incorporates a modular architecture that can extend the database with additional services. Replication services can extend the database to a cluster version and thus help with scaling and fault tolerance.

The SQL Server Analysis Services, augment the database with OLAP and data mining capabilities, making the database ideal for the workloads that we care about in this guide.

Updating your Zendesk data on MS SQL Server

As you will be generating more data on Zendesk, you will need to update your older data on an MS SQL Server database. This includes new records together with updates to older records that for any reason have been updated on Zendesk.

You will need to periodically check Zendesk for new data and repeat the process that has been described previously while updating your currently available data if needed. Updating an already existing row on a SQL Server table is achieved by creating UPDATE statements.

Another issue that you need to take care of is the identification and removal of any duplicate records on your database. Either because Zendesk does not have a mechanism to identify new and updated records or because of errors on your data pipelines, duplicate records might be introduced to your database.

In general, ensuring the quality of the data that is inserted in your database is a big and difficult issue and MS SQL Server features like TRANSACTIONS can help tremendously, although they do not solve the problem in the general case.

The best way to load data from Zendesk to MS SQL Server

So far we just scraped the surface of what you can do with MS SQL Server and how to load data into it. Things can get even more complicated if you want to integrate data coming from different sources.

Are you striving to achieve results right now?

Instead of writing, hosting, and maintaining a flexible data infrastructure use RudderStack that can handle everything automatically for you.

RudderStack with one click integrates with sources or services, creates analytics-ready data, and syncs your Zendesk to MS SQL Server right away.

Sign Up For Free And Start Sending Data

Test out our event stream, ELT, and reverse-ETL pipelines. Use our HTTP source to send data in less than 5 minutes, or install one of our 12 SDKs in your website or app.

Get Started Image

Get started today

Start building smarter customer data pipelines today with RudderStack. Our solutions engineering team is here to help.

Customer Data Platform for Developers | RudderStack
HIPPA Compliant
SOC 2 TYPE 2Users love RudderStack on G2