How to read JIRA data in SSIS – Call REST API / Load to SQL Server

Introduction

UPDATE: ZappySys has released a brand new API Connector for Jira Online which makes it much simpler to Read/Write Jira Data in SSIS 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.

Please visit this page to see all Pre-Configured ready to use API connectors which you can use in SSIS API Source / SSIS API Destination OR API ODBC Driver (for non-SSIS Apps such as Excel, Power BI, Informatica).

jira-logoIn this blog, we will learn How to read JIRA data in SSIS and load 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 SSIS.

We will go through the steps to read data from JIRA and Load into SQL Server using SSIS JSON Source Connector.

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

So let’s get started.

Prerequisite

  1. First, you will need to have SSIS installed
  2. Secondly, make sure to have SSDT
  3. Finally, do not forget to install ZappySys SSIS PowerPack

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

In order to start, we will show several examples. ZappySys includes an SSIS JSON Source Connector that will help you to call JIRA API, read various data from JIRA with SSIS 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.

Load JIRA data to SQL Server using SSIS JSON Source

Now once you have API token in the JIRA Atlassian Cloud website we can move to SSIS Part.

  1. Create a new SSIS Project or Open Existing Project in Visual Studio  (File > New > Project > Business Intelligence > Integration Services)
  2. Once SSIS Package is open, Goto Data Flow Section, Drag and Drop ZS JSON Source Component from the SSIS Toolbox.
    Drag and Drop JSON Source Component

    Drag and Drop JSON Source Component

  3. Double click on that Component to Edit the Configurations. Select File path or web URL from the Access Mode drop-down.
    Enter Web URL like this:
    Here, your-domain is your Atlassian Cloud account’s domain name (we are using zappysys.atlassian.net).

    * startAt is the index of the first item returned in the page of results.
    * maxResults the maximum number of items that can be returned per page. 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)

    JSON Source: Enter the Request URL

    JSON Source: Enter the Request URL

  4. Check Use Credentials Option and select a new ZS-HTTP Connection from the drop-down.
  5. Configure new HTTP Connection for JIRA as below.
    Basically, you need to enter 3 pieces on the General tab (i.e. Auth URL, Atlassian Account UserName, API token)

    JIRA Connection: HTTP Connection Manager

    JIRA Connection: HTTP Connection Manager

  6. now, we are going to set Pagination related configurations for JIRA Search request. For example, if you want to read 50 records in each response and keep doing it until last page found then set startAt=0, maxResults=50 and increment startAt by 50.
    Do not to submit your App for Marketplace Approval (Last step) if you are creating OAuth App to access their own company data and do not want to access other company’s data using your app. In other words leave your app under Development mode only (Ignore production credentials if you do not wish to publish app to marketplace).
    ssis-json-source-pagination-tab-jira-request-configurations

    JSON Source – JIRA request Pagination Configurations Settings

  7. Select Filter, Click on the Preview button to see the response data.
    JSON Source: Preview Issues/tickets of JIRA

    JSON Source: Preview Issues/tickets of JIRA

  8. Click OK to save JSON Source settings.
  9. Drag any destination (i.e. OLEDB Destination) from your SSIS toolbox. For demo purpose, we will load JIRA Issues data into SQL Server Table.
    Drag and Drop OLE DB Destination Component

    Drag and Drop OLE DB Destination Component

  10. Configure OLEDB Destination (Select Connection and Target Table). You can Create a new Table for the destination by clicking NEW next to Table drop-down.
    OLE DB Destination Component - Configure for dumping JIRA data in SQL Table

    OLE DB Destination Component – Configure for dumping JIRA data in SQL Table

  11. Click on the Columns Tab to configure Mappings. Click OK to save.
    OLE DB Destination - Column Configurations

    OLE DB Destination – Column Configurations

  12. You can see the requests in Fiddler.
    Fiddler - See All The Requests

    Fiddler – See All The Requests

  13. And that’s it. Execute your package to Load JIRA data to SQL Server.

Conclusion

In this blog, we learned how to Generate a JIRA API Token, retrieve JIRA Issues/tickets data from JIRA with SSIS using ZS JSON Source Component in a very simple way. You can achieve many more functionalities with this tool. Check our blogs/articles on JSON Source Component https://zappysys.com/blog/tag/ssis-json-source/ 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 REST API Integration, SSIS JSON Source (File/REST) and tagged , , , , .