{"id":132,"date":"2015-10-27T20:18:41","date_gmt":"2015-10-27T20:18:41","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=132"},"modified":"2026-03-11T00:31:26","modified_gmt":"2026-03-11T00:31:26","slug":"read-zendesk-data-in-ssis-call-rest-api","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-zendesk-data-in-ssis-call-rest-api\/","title":{"rendered":"How to read Zendesk data in SSIS with REST API"},"content":{"rendered":"

Introduction<\/h2>\n
UPDATE:<\/strong>\u00a0ZappySys has released a brand new\u00a0Zendesk Online connector,<\/a>\u00a0which makes it much simpler to\u00a0read\/write Zendesk Data in SSIS<\/strong> compared to the steps listed in this article. You can still use the steps from this article, but if you are new to APIs or want to avoid the learning curve, use a newer approach.<\/p>\n<\/div><\/div>\n

\"\"<\/a>Zendesk<\/a> is one of the most popular customer service platforms available today.\u00a0Zendesk offers a REST API<\/a> to interact with its cloud-based service programmatically (to manage or read data).<\/span> The problem is that not everyone is a programmer, and not everyone has time to learn how to write C# or JAVA code to access a REST API.<\/p>\n

In this article, you will learn how to get Zendesk data using the\u00a0SSIS JSON Source (REST API Connector).<\/a> This connector allows you to read Zendesk data in SSIS using REST API Calls without any scripting\/programming.\u00a0Techniques mentioned in this article can also be used to consume data from other services (e.g.,\u00a0read data from Twitter<\/a>).<\/span>\u00a0In this article, you will see step-by-step instructions to read Zendesk ticket information and load it into on-premises SQL Server.<\/p>\n

Also, check our other article\u00a0on\u00a0how<\/a><\/span> to call the Zendesk REST API to create a new ticket<\/a>.<\/p>\n

Different methods for Zendesk REST API Access<\/h2>\n

Zendesk allows you to access its data in three different ways.<\/p>\n

    \n
  1. REST API access using OAuth Access Token<\/li>\n
  2. REST API access using API Token<\/li>\n
  3. REST API access using Basic Authentication (Use userid (e.g., email) and Password)<\/li>\n<\/ol>\n

    Each method has pros and cons. The simplest method is the third, which is easy to use but requires passing your account password with each call. The second method is also easy to use but requires an additional step to create an access token. The first method is the most secure, but it has a few extra steps compared to the other methods.<\/p>\n

    Prerequisite<\/span><\/h2>\n

    Before you can finish the tasks explained in this article, you have to finish the following steps. This article assumes you have basic knowledge of SSIS (SQL Server Integration Services)<\/p>\n

      \n
    1. Make sure you have the SSIS designer installed. Sometimes it’s referred to as BIDS or SSDT (Get from here)<\/a><\/li>\n
    2. Download SSIS PowerPack<\/a>\u00a0(FREE Trial – 30 days).<\/li>\n
    3. Optional \u2013 Another very useful Free tool is Fiddler. You can use it to see raw request\/response (Check this article<\/a>)<\/li>\n<\/ol>\n

      Method-1: Zendesk API Access using OAuth in SSIS<\/h2>\n

      To read about the OAuth Method, click here<\/a><\/p>\n

      Method-2: Zendesk API Access using Token in SSIS<\/h2>\n

      Now let’s look at the second method for accessing Zendesk data using SSIS.<\/p>\n

      Create Token in Zendesk Portal for REST API Access<\/h3>\n
        \n
      1. Log in to your account https:\/\/{{your-subdomain}}.zendesk.com\/login and then click on the settings icon<\/li>\n
      2. On the next page, click on the API link<\/li>\n
      3. On the API page, scroll to the API Access Tokens section. Make sure the Token Access<\/strong> method is checked.<\/li>\n
      4. Click on the ” add new token hyperlink. Once prompted, give a name to the Token token (this is just a label)<\/li>\n
      5. Once the above steps are done, a token should be created and listed under your API Access page. The token will be used as your Password to access the REST API (will be shown in the next section). For more information, check this link >>> \u00a0How to generate a token for ZenDesk API Call<\/a>.\n
        \"\"<\/a>

        Create a new Zendesk REST API Access Token<\/p><\/div><\/li>\n<\/ol>\n

        Configure SSIS HTTP Connection<\/h3>\n

        Perform the following steps to extract data from Zendesk and load it into SQL Server. The steps below assume that you are using the API Token method (#2 from the supported methods to call the Zendesk API). This is better than using Userid and Password.<\/p>\n

          \n
        1. Open a new SSIS Project.<\/li>\n
        2. First of all, drag and drop the Data Flow Task from the SSIS Toolbox and double-click it to edit.\n
          <\/div>\n
        3. From the SSIS toolbox, drag and drop the\u00a0ZS JSON Source<\/a> onto the data flow designer surface, then double-click it to edit.\n
        4. From the AccessMode dropdown [Url From Connection<\/strong>]<\/li>\n
        5. Assume you want to extract all tickets from Zendesk, then enter the following URL (in the screenshot, we appended?page=1, which is optional)\n
          https:\/\/YOUR_SUB_DOMAIN.zendesk.com\/api\/v2\/tickets.json<\/pre>\n<\/li>\n
        6. Check the Use Credentials<\/strong> option and select New ZS-HTTP connection. When prompted
          \nFor Credential Type: select Basic Authentication<\/strong> \u00a0(Use of UserID and Token ).
          \nFor Web URL: Enter any URL (This will be ignored anyway because the URL entered on the JSON Source UI will be used)
          \nFor user name : enter YOUR_EMAIL\/token \u00a0(e.g. bob@mycompany.com\/token)
          \n<\/strong>For Password: enteToken token obtained from the Admin console > API section. For more information, check this link >>> \u00a0          How to generate a token for ZenDesk API Call<\/a>.
          \n
          NOTE: Appending \/token command in Userid will allow us to pass the token rather than the Password of that account. This approach is more secure because you can disable the Token at any time without deactivating the user account. If you want to access data using your userid and Password rather than a token, then do not use \/token<\/strong>\u00a0after your email. By default, uToken Token and the use of Password are not allowed unless you enable these methods from the Admin console (See above section)<\/div><\/div>\n
          <\/div>\n
          \"\"<\/a>
          Configure SSIS HTTP Connection – Use Zendesk Token to Call REST API<\/p><\/div><\/li>\n<\/ol>\n
          Adding Retry Settings for HTTP or OAuth connection<\/h3>\n
          Zendesk enforces an API rate limit, which means you cannot issue too many API calls within a given time frame. Check this document<\/a> for exact information.<\/p>\n
          ZappySys provides API retry settings on both the OAuth Connection and the HTTP Connection. For Zendesk, you can retry on a specific status code 429<\/strong><\/p>\n
            \n
          1. Open connection manager UI<\/li>\n
          2. Go to the Retry-Settings page<\/li>\n
          3. Select Retry web error on matching response status code<\/strong>.<\/li>\n
          4. Enter 429<\/strong> for the status code<\/li>\n
          5. Check multiple wait times<\/strong><\/li>\n
          6. In the wait time, enter 5000<\/strong> (wait 5 seconds or multiple of 5 seconds)<\/li>\n
          7. Set the max retry count to 6. <\/strong>See the example (It’s not for Zendesk, but will give you an idea)\n
            \"\"<\/a>
            OAuth connection – Retry Options<\/p><\/div><\/li>\n<\/ol>\n
            Configure Zendesk API Pagination and Filter for SSIS JSON Source<\/h3>\n
              \n
            1. Now click [Select Filter], select the tickets node (see the array icon), and click OK. It will generate the following Filter Expression to extract all records found under the tickets array node (e.g., tickets : [ {…} , {…} … ] )\n
              $.tickets[*]\r\n<\/pre>\n
              <\/div>\n
              \"\"<\/a>
              Select Filter – Extract Data from Zendesk API Response (Read Ticket)<\/p><\/div><\/li>\n
            2. N, let’sts configure pagination. By default, Zendesk API doesn’t return all records, so you have to specify pagination as below. Click on the Pagination<\/strong> Tab. Enter $.next_page<\/em> for Next URL attribute as below (you can also Browse Path)\n
            3. Now time to click Preview. <\/strong>If\u00a0you followed everything correctly, then when you click the Preview<\/strong> Button, you should see some data in the preview (assuming you have at least one ticket).\n
              <\/div>\n
            4. Now click the Columns tab<\/strong> to review data types (you can change the default length and check the Lock option on the right side). This change is needed because the datatype guess happens based on the design-time setting (only 300 rows scanned by default). At runtime, if you get a truncation-related error, then copy the error message into Notepad to get the exact steps and column name that caused the error. If you need a length greater than 4000 chars, use the DT_NTEXT data type instead (change the datatype and click the column name cell on the right to save the change).<\/li>\n
            5. Click OK<\/strong> to save UI<\/li>\n<\/ol>\n
              How to extract Zendesk data using Incremental API<\/h3>\n
              There will be a time when you need to extract data incrementally (i.e., Extract only the changed data since the last load). Zendesk API supports many Incremental API endpoints<\/a>. If you are using this endpoint, you have to make sure of a few things.<\/p>\n