{"id":5389,"date":"2018-11-17T11:37:44","date_gmt":"2018-11-17T11:37:44","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=5389"},"modified":"2025-03-19T07:34:55","modified_gmt":"2025-03-19T07:34:55","slug":"load-jira-data-sql-server-odbc-call-rest-api","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/load-jira-data-sql-server-odbc-call-rest-api\/","title":{"rendered":"Load JIRA data into SQL Server with ODBC – Call REST API"},"content":{"rendered":"

Introduction<\/span><\/h2>\n
UPDATE:<\/strong>\u00a0ZappySys has released a brand new API Connector for Jira Online<\/a> which makes it much simpler to\u00a0Read\/Write Jira Data in ODBC<\/strong> compared to the steps listed in this article. You can still use steps from this article but if you are new to API or want to avoid learning curve with API then use newer approach.<\/p>\n

Please visit this page to see all<\/a>\u00a0Pre-Configured ready to use API connectors which you can use in SSIS API Source<\/a> \/ SSIS API Destination<\/a> OR\u00a0API ODBC Driver<\/a> (for non-SSIS Apps such as Excel, Power BI, Informatica).<\/p>\n<\/div><\/div>\n\"jira-logo\"<\/a>In this blog, we will learn\u00a0How to Load JIRA data in ODBC and load JIRA data into SQL Server Table<\/strong>, along with few other topics such as how to generate an API Token using Atlassian Account for\u00a0JIRA REST API Call<\/strong>, how to read all Issues data from JIRA with ODBC.<\/p>\n

We will go through the steps to read data from JIRA and Load into SQL Server using\u00a0JSON Driver of ODBC PowerPack<\/a><\/p>\n

In nutshell,\u00a0this post will focus on how to call JIRA API using ODBC PowerPack.<\/p>\n

So let\u2019s get started.<\/p>\n

Requirements<\/span><\/h2>\n
    \n
  1. A first requirement will be to SQL Server Database Engine Installed<\/li>\n
  2. The second requirement will be SSMS installed<\/li>\n
  3. Finally, make sure to have\u00a0ZappySys ODBC PowerPack<\/a>\u00a0installed.<\/li>\n<\/ol>\n

    What is JIRA<\/h2>\n

    JIRA is Atlassian\u2019s popular project- and issue-tracking platform used by development and technical support teams to get more work done and faster.<\/p>\n

    It combines:<\/p>\n

    issue tracking<\/strong> – a software application that allows to record and follow the progress of every problem or “issue” that user identifies until the problem is resolved.
    \nagile project management<\/strong> – an iterative approach to planning and guiding project processes. customizable workflow, and a pluggable integration – integrates with Freshdesk, GitHub, Zendesk, Zapbook, Asana and so on.<\/p>\n

    Getting Started<\/h2>\n

    First of all, we will see several examples. ZappySys includes an\u00a0ODBC JSON Source Connector<\/a>\u00a0that will help you to call JIRA API, read various data from JIRA with ODBC and do many more actions using REST API. To learn more about JIRA API check this help file<\/a>.<\/p>\n

    Generate API Token<\/h2>\r\nTo access JIRA data using REST API call you need to generate an API Token in your Atlassian account.\u00a0Follow the steps mentioned below to generate API Token:\r\n
      \r\n \t
    1. you can directly login to the JIRA Atlassian Cloud site<\/a> with your credentials, and redirect to the JIRA Atlassian Cloud Account's\u00a0Profile Page<\/a> for generating an API Token.<\/li>\r\n \t
    2. Click\u00a0API tokens<\/strong>, then\u00a0Create API token<\/strong>.\r\n
      \r\n\r\n\"Generate<\/a>\r\n

      Generate API Token - Step - 1<\/p>\r\n\r\n<\/div><\/li>\r\n \t

    3. Create API Token - Give Label name to your API token which you are generating now\r\n
      \r\n\r\n\"Generate<\/a>\r\n

      Generate API Token - Step - 2<\/p>\r\n\r\n<\/div><\/li>\r\n \t

    4. Use\u00a0Copy to the clipboard<\/strong>,\u00a0and paste the token into your or elsewhere:\r\n
      Note:\u00a0 * For security reasons it isn't possible to view the token after closing the creation dialog; if necessary, create a new token. * You should store the token securely, just as for any password.<\/blockquote><\/div>\r\n
      \r\n\r\n\"Generate<\/a>\r\n

      Generate API Token - Step - 3<\/p>\r\n\r\n<\/div><\/li>\r\n \t

    5. If you forgot API token, then you can Revoke your old API token and create new API token OR you can create multiple API tokens too.\r\n
      \r\n\r\n\"Generate<\/a>\r\n

      Generate API token - Step - 4<\/p>\r\n\r\n<\/div><\/li>\r\n<\/ol>\r\nNow we have successfully generated an API token. Let\u00b4s start with an example. In this example, we will show how to read Tasks\/Issues data from JIRA and store that data into a SQL table with SSIS.<\/div>\n

      About JIRA REST API<\/h2>\n

      JIRA On premises and Cloud Version both offers REST API. Here are some examples of\u00a0JIRA REST API<\/a>\u00a0.\u00a0For reading JIRA REST API data we will use JIRA REST API Search method<\/a>\u00a0which returns data in JSON format. Search API support use of JQL Syntax<\/a> to filter issues. If you don’t specify JQL<\/strong> parameters in \/search endpoint then all issues will be returned. Check this link to learn how to build JQL for JIRA REST API Call<\/a>. If you don’t have JIRA hosted instance yet but still want to try REST API Examples on public JIRA Server then try below URL (Its official JIRA site which lists their own issues). For example try to copy below URL in browser and hit enter.<\/p>\n

      https:\/\/jira.atlassian.com\/rest\/api\/2\/search?jql=project=JRASERVER<\/pre>\n
      Step-by-Step: Import JIRA data REST API into SQL<\/span><\/h2>\n
      Now once you have API token in the JIRA Atlassian Cloud website we can move to ODBC JSON Driver Configuration Part. For this example purpose you can use the following URL:<\/p>\n
      https:\/\/YOUR-DOMAIN.atlassian.net\/rest\/api\/2\/search?startAt=0&maxResults=100&jql=project=MYPROJECT<\/pre>\n
      hence,\u00a0your-domain is your Atlassian Cloud account\u2019s domain name (we are using zappysys2.atlassian.net).<\/strong>
      \nTherefore\u00a0startAt<\/strong>\u00a0is the index of the first item returned in the page of results.
      \nAnd another\u00a0maxResults<\/strong>\u00a0is the maximum number of items that can be returned per page.<\/p>\n
      Above all, each API endpoint may have a different limit for the number of items returned, and these limits may change without notice. (For more See this\u00a0JIRA API Documentation for Pagination<\/a>)<\/p>\n
      Create ODBC \u2013 Native Zappysys JSON Driver Data Source<\/span><\/h3>\n
        \n
      1. To do this, first of all, we will open the ZappySys Data Gateway Configuration:\n
      2. Add the Native – ZappySys JSON Driver Data source.\n
      3. Now it’s time to connect with JIRA. Let’s use JSON Driver Data source with the\u00a0following URL:\n
        \n
        \n
        https:\/\/zappysys2.atlassian.net\/rest\/api\/2\/search?startAt=0&maxResults=100\r\n<\/pre>\n<\/div>\n<\/div>\n
        \"ODBC<\/a>
        ODBC JSON Driver: Enter Request URL<\/p><\/div><\/li>\n
      4. It is time to configure the Other Settings. Select HTTP Connection Type from the Connection Type drop-down and Click on Click to Configure Link to Configure the Connection Manager.\n
      5. Now, we are going to set Pagination related configurations for JIRA Search request. Page number indicator must be startAt<\/span>\u00a0 and maxResults<\/span>\u00a0 value in URL must match with Increment By (i.e. 100)\n
      6. After adding pagination parameters let’s add JQL=<\/span> search parameter to get specific data. Here we are searching the data by Project name and status. You can find more information about JQL on this link:\u00a0Search JIRA like a boss with JQL<\/a>\n
      7. Click on Select Filter to set the Filter.\n
      8. You are done with the Configurations, Click on Test Connection to test the Connection.\n
      9. After that select the table from where you want to get data.\n
      10. Click on the Preview Data button to preview the data.\n
      11. Now using Code Generator we will generate the query.\n
        \"ODBC<\/a>
        Generate the query<\/p><\/div><\/li>\n<\/ol>\n
        Load JIRA data in MS SQL Server<\/span><\/h3>\n
        Once you configured the data source in Gateway, we can now setup Linked Server in SQL Server to query API data.\r\n
          \r\n \t
        1. Assuming you have installed SQL Server and SSMS. If not then get both for FREE from here:\u00a0Get SQL Server Express<\/a>\u00a0and\u00a0\u00a0Get SSMS<\/a><\/li>\r\n \t
        2. Open SSMS and connect to SQL Server.<\/li>\r\n \t
        3. Go to Root > Server Objects > Linked Servers node. Right click and click\u00a0New Linked Server...<\/strong>\r\n
          \r\n\r\n\"Add<\/a>\r\n
          Add Linked Server in SQL Server<\/p>\r\n\r\n<\/div><\/li>\r\n \t
        4. \u00a0Now enter the linked server name, select Provider as SQL Native Client<\/li>\r\n \t
        5. Enter data source as\u00a0GatewayServerName,PORT_NUMBER<\/span><\/strong>\u00a0where server name is where ZappySys Gateway is running (Can be same as SQL Server machine or remote machine). Default PORT_NUMBER is 5000 but confirm on Data gateway > General tab in case its different.<\/li>\r\n \t
        6. Enter Catalog Name. This must match name from Data gateway Data sources grid > Name column\r\n
          \r\n\"Configure\r\n<\/a>\r\n
          Configure Linked Server Provider, Catalog, Server, Port for ZappySys Data Gateway Connection<\/p>\r\n<\/div>\r\n
          \r\nINFO:<\/strong>\r\n