Load JIRA data into SQL Server with ODBC – Call REST API

Introduction

jira-logoIn this blog, we will learn How to Load JIRA data in ODBC and load JIRA data into SQL Server Table, along with few other topics such as how to generate an API Token using Atlassian Account for JIRA REST API Call, how to read all Issues data from JIRA with ODBC.

We will go through the steps to read data from JIRA and Load into SQL Server using JSON Driver of ODBC PowerPack

In nutshell, this post will focus on how to call JIRA API using ODBC PowerPack.

So let’s get started.

Requirements

  1. A first requirement will be to SQL Server Database Engine Installed
  2. The second requirement will be SSMS installed
  3. Finally, make sure to have ZappySys ODBC PowerPack installed.

What is JIRA

JIRA is Atlassian’s popular project- and issue-tracking platform used by development and technical support teams to get more work done and faster.

It combines:

issue tracking – a software application that allows to record and follow the progress of every problem or “issue” that user identifies until the problem is resolved.
agile project management – 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.

Getting Started

First of all, we will see several examples. ZappySys includes an ODBC JSON Source Connector that 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.

Generate API Token

To access JIRA data using REST API call you need to generate an API Token in your Atlassian account. Follow the steps mentioned below to generate API Token:
  1. you can directly login to the JIRA Atlassian Cloud site with your credentials, and redirect to the JIRA Atlassian Cloud Account's Profile Page for generating an API Token.
  2. Click API tokens, then Create API token.
    Generate API Token - Step - 1

    Generate API Token - Step - 1

  3. Create API Token - Give Label name to your API token which you are generating now
    Generate API Token - Step - 2

    Generate API Token - Step - 2

  4. Use Copy to the clipboard, and paste the token into your or elsewhere:
    Note:  * 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.
    Generate API Token - Step - 3

    Generate API Token - Step - 3

  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.
    Generate API token - Step - 4

    Generate API token - Step - 4

Now we have successfully generated an API token. Let´s 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.

About JIRA REST API

JIRA On premises and Cloud Version both offers REST API. Here are some examples of JIRA REST API . For reading JIRA REST API data we will use JIRA REST API Search method which returns data in JSON format. Search API support use of JQL Syntax to filter issues. If you don’t specify JQL parameters in /search endpoint then all issues will be returned. Check this link to learn how to build JQL for JIRA REST API Call. 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.

Step-by-Step: Import JIRA data REST API into SQL

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:

hence, your-domain is your Atlassian Cloud account’s domain name (we are using zappysys2.atlassian.net).
Therefore startAt is the index of the first item returned in the page of results.
And another maxResults is the maximum number of items that can be returned per page.

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 JIRA API Documentation for Pagination)

Create ODBC – Native Zappysys JSON Driver Data Source

  1. To do this, first of all, we will open the ZappySys Data Gateway Configuration:
    Open ZappySys Data Gateway

    Open ZappySys Data Gateway

  2. Add the Native – ZappySys JSON Driver Data source.
    Add Gateway Data Source (Native JSON Driver)

    Add Gateway Data Source (Native JSON Driver)

  3. Now it’s time to connect with JIRA. Let’s use JSON Driver Data source with the following URL:
    ODBC JSON Driver: Enter Request URL

    ODBC JSON Driver: Enter Request URL

  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.
    JIRA REST API Connection - Use ZappySys JSON Driver HTTP Connection

    JIRA REST API Connection – Use ZappySys JSON Driver HTTP Connection

  5. Now, we are going to set Pagination related configurations for JIRA Search request. Page number indicator must be startAt  and maxResults  value in URL must match with Increment By (i.e. 100)
    ODBC JSON Driver - JIRA request Pagination Configurations Settings

    ODBC JSON Driver – JIRA request Pagination Configurations Settings

  6. After adding pagination parameters let’s add JQL= 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: Search JIRA like a boss with JQL

    Search using jql parameter

    Search using JQL parameter

  7. Click on Select Filter to set the Filter.
    ODBC JSON Driver for JIRA API: Select Filter

    ODBC JSON Driver for JIRA API: Select Filter

  8. You are done with the Configurations, Click on Test Connection to test the Connection.
    ODBC JSON Driver - Test Connection

    ODBC JSON Driver – Test Connection

  9. After that select the table from where you want to get data.
    JSON Driver: Create Query of JSON data

    JSON Driver: Create Query of JSON data

  10. Click on the Preview Data button to preview the data.
    Preview issues table data

    Preview issues table data

  11. Now using Code Generator we will generate the query.
    ODBC - Generate the query

    Generate the query

Load JIRA data in MS SQL Server

Once you configured the data source in Gateway, we can now setup Linked Server in SQL Server to query API data.
  1. Assuming you have installed SQL Server and SSMS. If not then get both for FREE from here: Get SQL Server Express and  Get SSMS
  2. Open SSMS and connect to SQL Server.
  3. Go to Root > Server Objects > Linked Servers node. Right click and click New Linked Server...
    Add Linked Server in SQL Server

    Add Linked Server in SQL Server

  4.  Now enter the linked server name, select Provider as SQL Native Client
  5. Enter data source as GatewayServerName,PORT_NUMBER where 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.
  6. Enter Catalog Name. This must match name from Data gateway Data sources grid > Name column
    Configure Linked Server Provider, Catalog, Server, Port for ZappySys Data Gateway Connection

    Configure Linked Server Provider, Catalog, Server, Port for ZappySys Data Gateway Connection

  7. Click on Security Tab and select last option "Be made using this security context". Enter your gateway user account here.
  8. Click OK to save Linked Server
  9. In SSMS execute below SQL query to test your connectivity.
    SELECT * FROM OPENQUERY( MY_LINKED_SERVER_NAME, 'SELECT * FROM $')
  10. Here is the preview after you run some REST API query in SQL Server. Notice that you can override default configuration by supplying many parameters in WITH clause (second query example in the screenshot).
    ODBC - Query API Data in SQL Server ODBC - Query API Data in SQL Server

    SSMS Output - Query REST API via Linked Server OPENQUERY statement (Connect to ZappySys Data Gateway)

  11. You can wrap your queries inside View or wrap inside Stored procedure to parameterize. Here is an example of creating the view which calls REST API queries. Below View can be consumed like a normal table from any Tools or Programming Language which supports connectivity to SQL Server.
    CREATE VIEW dbo.vwApiInvoices 
    AS 
    /*Call REST API inside SQL Server View*/
    SELECT * FROM OPENQUERY( MY_LINKED_SERVER_NAME , 'SELECT * FROM $');
    
    GO
    
  12. Notice in above approach if you parameterize Stored Procedure then check this article to understand Dynamic Metadata.
  13. Now let's insert JIRA data into the new data table "tblissues"  in the SQL server database. For that, we need to execute below SQL query.
    SELECT * into tblissues FROM OPENQUERY( MY_LINKED_SERVER_NAME, 'SELECT * FROM $')
    Insert Data into the table Insert Data into the table
  14. Let's insert and update records into the already created table "tblissues" .
    • To do that first we need to insert the new JIRA data into the "#temp_tblissues" temporary database table.
    • Now let's delete the old records from the database table which are updated.
    • At the last insert all new JIRA data into the database table.
    • To do that, we need to query like below SQL queries.
    SELECT * into #temp_tblissues FROM OPENQUERY(MY_LINKED_SERVER_NAME, 'SELECT * FROM $')
    
    DELETE FROM [dbo].[tblissues] WHERE id in (SELECT id FROM #temp_tblissues)
    
    INSERT INTO tblissues
    SELECT * FROM #temp_tblissues
     
  15. In the upper step, we see how to insert and update new records. Now if you want to update particular fields records only then you need to query like this. In this query, We are updating "fields_duedate"  and "fields_issuetype_name"  fields only.
    Update dbti_1
    set dbti_1.fields_duedate = jira_2.fields_duedate,
    dbti_1.fields_issuetype_name = jira_2.fields_issuetype_name
    FROM tblissues dbti_1
    JOIN (SELECT * FROM OPENQUERY(MY_LINKED_SERVER_NAME, 'SELECT * FROM $')) jira_2 on dbti_1.id=jira_2.id
    * If you are getting error like : "Cannot resolve the collation conflict between “Latin1_General_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation" then you need to query like this :
    Update dbti_1
    set dbti_1.fields_duedate = jira_2.fields_duedate,
    dbti_1.fields_issuetype_name = jira_2.fields_issuetype_name
    FROM tblissues dbti_1
    JOIN (SELECT top 1 * FROM OPENQUERY(MY_LINKED_SERVER_NAME, 'SELECT * FROM $')) jira_2 
    on dbti_1.id=jira_2.id 
    COLLATE SQL_Latin1_General_CP1_CI_AS
     

Conclusion

In this blog, we learned how to generate a JIRA API Token, Load JIRA data of Issues/tickets table using ODBC JSON / REST API Driver in a very simple way. You can achieve many more functionalities with this tool. Check our blogs/articles on JSON / REST API Driver https://zappysys.com/blog/category/odbc-powerpack/odbc-drivers/json-rest-api-driver/ to find out what this tool is capable of more.

References

Finally, you can use the following links for more information about the use of JIRA API with our tools:

Posted in JSON File / REST API Driver, ODBC Drivers, ODBC PowerPack, REST API and tagged , , , , , .