Introduction
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).
In 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
- First, you will need to have SSIS installed
- Secondly, make sure to have SSDT
- 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:- 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.
- Click API tokens, then Create API token.
Generate API Token - Step - 1
- Create API Token - Give Label name to your API token which you are generating now
Generate API Token - Step - 2
- 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
- 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
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.
- Create a new SSIS Project or Open Existing Project in Visual Studio (File > New > Project > Business Intelligence > Integration Services)
- 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
- 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).1https://your-domain.atlassian.net/rest/api/2/search?startAt=0&maxResults=50* 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
- Check Use Credentials Option and select a new ZS-HTTP Connection from the drop-down.
- 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
- 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).
JSON Source – JIRA request Pagination Configurations Settings
- Select Filter, Click on the Preview button to see the response data.
JSON Source: Preview Issues/tickets of JIRA
- Click OK to save JSON Source settings.
- 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
- 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
- Click on the Columns Tab to configure Mappings. Click OK to save.
OLE DB Destination – Column Configurations
- You can see the requests in Fiddler.
Fiddler – See All The Requests
- 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:
- JIRA Documentation for API
- Landing Page for JSON Source Component, you can also find Tutorial Video here.
- Help File: Documentation of JSON Source Component.
