Zendesk Connector for SSRS
Zendesk Connector can be used to integrate Zendesk REST API in your App / BI Tools. You can read/write data about Tickets, Activity, Users, Organizations and more.
In this article you will learn how to quickly and efficiently integrate Zendesk data in SSRS without coding. We will use high-performance Zendesk Connector to easily connect to Zendesk and then access the data inside SSRS.
Let's follow the steps below to see how we can accomplish that!
Zendesk Connector for SSRS is based on ZappySys API Driver which is part of ODBC PowerPack. It is a collection of high-performance ODBC drivers that enable you to integrate data in SQL Server, SSIS, a programming language, or any other ODBC-compatible application. ODBC PowerPack supports various file formats, sources and destinations, including REST/SOAP API, SFTP/FTP, storage services, and plain files, to mention a few.
Video Tutorial - Integrate Zendesk data in SSRS
This video covers the following topics and more, so please watch carefully. After watching the video, follow the steps outlined in this article:
- How to download and install the required PowerPack for Zendesk integration in SSRS
- How to configure the connection for Zendesk
- Features of the ZappySys API Driver (Authentication / Query Language / Examples / Driver UI)
- How to use the Zendesk in SSRS
Create Data Source in Data Gateway based on ZappySys API Driver
In this section we will create a data source for Zendesk in Data Gateway. Let's follow these steps to accomplish that:
-
Download and install ODBC PowerPack.
-
Search for
gatewayin Windows Start Menu and open ZappySys Data Gateway Configuration:
-
Go to Users tab and follow these steps to add a Data Gateway user:
- Click Add button
-
In Login field enter username, e.g.,
john - Then enter a Password
- Check Is Administrator checkbox
- Click OK to save
-
Now we are ready to add a data source:
- Click Add button
- Give Datasource a name (have it handy for later)
- Then select Native - ZappySys API Driver
- Finally, click OK
ZendeskDSNZappySys API Driver
-
When the Configuration window appears give your data source a name if you haven't done that already, then select "Zendesk" from the list of Popular Connectors. If "Zendesk" is not present in the list, then click "Search Online" and download it. Then set the path to the location where you downloaded it. Finally, click Continue >> to proceed with configuring the DSN:
ZendeskDSNZendesk
-
Now it's time to configure the Connection Manager. Select Authentication Type, e.g. Token Authentication. Then select API Base URL (in most cases, the default one is the right one). More info is available in the Authentication section.
Zendesk authentication
Authenticate using Static Token instead of userid / password ([Token Access] option must be enabled under Admin Console > Channel > API > Settings and then Click [ADD API Token] to generate new token. These settings typically found here https://YOUR-SUB-DOMAIN.zendesk.com/agent/admin/api/settings [API reference]
Please refer to external API reference
API Connection Manager configuration
Just perform these simple steps to finish authentication configuration:
-
Set Authentication Type to
Token Authentication [Http] - Optional step. Modify API Base URL if needed (in most cases default will work).
- Fill in all the required parameters and set optional parameters if needed.
- Finally, hit OK button:
ZendeskDSNZendeskToken Authentication [Http]https://[$Domain$].zendesk.com/api/v2Required Parameters Sub Domain (e.g. mycompany) Fill-in the parameter... UserId Fill-in the parameter... Token Fill-in the parameter... Optional Parameters RetryMode RetryWhenStatusCodeMatch RetryStatusCodeList 429 RetryCountMax 5 RetryMultiplyWaitTime True 
Zendesk authentication
OAuth App must be created under Admin Console > Channel > API > Settings. These settings typically found here https://YOUR-SUB-DOMAIN.zendesk.com/agent/admin/api/oauth_clients [API reference]
Please refer to external API reference
API Connection Manager configuration
Just perform these simple steps to finish authentication configuration:
-
Set Authentication Type to
OAuth Authentication [OAuth] - Optional step. Modify API Base URL if needed (in most cases default will work).
- Fill in all the required parameters and set optional parameters if needed.
- Press Generate Token button to generate the tokens.
- Finally, hit OK button:
ZendeskDSNZendeskOAuth Authentication [OAuth]https://[$Domain$].zendesk.com/api/v2Required Parameters Sub Domain (e.g. mycompany) Fill-in the parameter... ClientId Fill-in the parameter... ClientSecret Fill-in the parameter... Permissions Fill-in the parameter... RedirectUrl Fill-in the parameter... Optional Parameters RetryMode RetryWhenStatusCodeMatch RetryStatusCodeList 429 RetryCountMax 5 RetryMultiplyWaitTime True 
Zendesk authentication
Authenticate using your userid / password (2FA must be OFF and [Password Access] option must be enabled under Admin Console > Channel > API > Settings. These settings typically found here https://YOUR-SUB-DOMAIN.zendesk.com/agent/admin/api/settings [API reference]
Please refer to external API reference
API Connection Manager configuration
Just perform these simple steps to finish authentication configuration:
-
Set Authentication Type to
UserID/Password Authentication (2FA must be OFF) [Http] - Optional step. Modify API Base URL if needed (in most cases default will work).
- Fill in all the required parameters and set optional parameters if needed.
- Finally, hit OK button:
ZendeskDSNZendeskUserID/Password Authentication (2FA must be OFF) [Http]https://[$Domain$].zendesk.com/api/v2Required Parameters Sub Domain (e.g. mycompany) Fill-in the parameter... UserName (2FA Must be OFF) Fill-in the parameter... Password Fill-in the parameter... Optional Parameters RetryMode RetryWhenStatusCodeMatch RetryStatusCodeList 429 RetryCountMax 5 RetryMultiplyWaitTime True
-
Set Authentication Type to
-
Once the data source connection has been configured, it's time to configure the SQL query. Select the Preview tab and then click Query Builder button to configure the SQL query:
ZappySys API Driver - ZendeskZendesk Connector can be used to integrate Zendesk REST API in your App / BI Tools. You can read/write data about Tickets, Activity, Users, Organizations and more.ZendeskDSN
-
Start by selecting the Table or Endpoint you are interested in and then configure the parameters. This will generate a query that we will use in SSRS to retrieve data from Zendesk. Hit OK button to use this query in the next step.
SELECT * FROM Tickets
Some parameters configured in this window will be passed to the Zendesk API, e.g. filtering parameters. It means that filtering will be done on the server side (instead of the client side), enabling you to get only the meaningful datamuch faster . -
Now hit Preview Data button to preview the data using the generated SQL query. If you are satisfied with the result, use this query in SSRS:
ZappySys API Driver - ZendeskZendesk Connector can be used to integrate Zendesk REST API in your App / BI Tools. You can read/write data about Tickets, Activity, Users, Organizations and more.ZendeskDSNSELECT * FROM Tickets
You can also access data quickly from the tables dropdown by selecting <Select table>.AWHEREclause,LIMITkeyword will be performed on the client side, meaning that thewhole result set will be retrieved from the Zendesk API first, and only then the filtering will be applied to the data. If possible, it is recommended to use parameters in Query Builder to filter the data on the server side (in Zendesk servers). -
Click OK to finish creating the data source.
-
Very important step. Now, after creating or modifying the data source make sure you:
- Click the Save button to persist your changes.
- Hit Yes, once asked if you want to restart the Data Gateway service.
This will ensure all changes are properly applied:
Skipping this step may result in the new settings not taking effect and, therefore you will not be able to connect to the data source.
Read data in SSRS from ZappySys Data Gateway
-
Open Visual Studio and create a new SSRS project.
-
Then add a new Shared Data Source (you can create a non-shared data source inside report too):
-
Continue with creating the Shared Data Source. Select Microsoft SQL Server as Type and hit Build button to proceed further:
-
Once a window opens, configure it similarly. Configure "ZendeskDSN" as database name. Finally, hit Test Connection and OK:
ZendeskDSN
-
Another window opens, and it should look similarly to this one below which ends the creation of a Data Source:
DataSource=localhost,5000;Initial Catalog=ZendeskDSN
-
Now it's time to create a Dataset. If you don't have a report created, in one of the wizard's steps it will look like this:
SELECT * FROM Tickets
-
Finally, once you complete the report, similar results will show up:
Passing Parameters to SSRS Report / Filter data
If you want to parameterize your report, then refer to this article
Actions supported by Zendesk Connector
Learn how to perform common Zendesk actions directly in SSRS with these how-to guides:
- Create Organization
- Create Ticket (Single)
- Create Tickets (Bulk)
- Create User
- Delete Attachment
- Delete Organization
- Delete Ticket
- Delete Tickets (Bulk)
- Delete Upload Session
- Delete User
- Read Attachment
- Read Brands
- Read Current User Info
- Read Groups
- Read Macros
- Read Organization Count
- Read Organization Fields
- Read Organization(s) by ID
- Read Organizations (All)
- Read Organizations (Incremental) changed after a specified date
- Read Requests
- Read Requests Search
- Read Sessions
- Read Tags
- Read Ticket ActivityStream
- Read Ticket ActivityStream Details
- Read Ticket Attachments
- Read Ticket Comments
- Read Ticket Events (Incremental) changed after a specified date
- Read Ticket Fields
- Read Ticket Forms
- Read Ticket Metric Events (Incremental) changed after a specified date
- Read Ticket Metrics
- Read Ticket(s) by ID
- Read Tickets (All)
- Read Tickets (Incremental) changed after a specified date
- Read Tickets (Recent)
- Read Tickets Count (All)
- Read Tickets Count (for Organization)
- Read Tickets for Organization
- Read Tickets for User (Assigned)
- Read Tickets for User (CCed)
- Read Tickets for User (Requested)
- Read User Fields
- Read User Sessions
- Read User(s) by ID
- Read Users (All)
- Read Users (Incremental) changed after a specified date
- Read Views
- Search Records - NO Sorting (Allows more than 1000 rows)
- Search Records - WITH Sorting and Extra Columns (Max 1000 rows)
- Update Organization
- Update Ticket (Single)
- Update Tickets (Bulk)
- Update User
- Upload Attachment (Single file)
- Upsert Organization - Create or Update
- Upsert User - Create or Update
- Upsert Users (Bulk) - Create or Update
- Make Generic API Request
- Make Generic API Request (Bulk Write)
Conclusion
In this article we showed you how to connect to Zendesk in SSRS and integrate data without any coding, saving you time and effort.
We encourage you to download Zendesk Connector for SSRS and see how easy it is to use it for yourself or your team.
If you have any questions, feel free to contact ZappySys support team. You can also open a live chat immediately by clicking on the chat icon below.
Download Zendesk Connector for SSRS Documentation
