IntroductionIn 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.
- 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.
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.
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 TokenTo 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.
- Create API Token - Give Label name to your API token which you are generating now
- Use Copy to the clipboard, and paste the token into your or elsewhere:
- 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.
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.
- 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:
* 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)
- 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)
- 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.
- Select Filter, Click on the Preview button to see the response data.
- 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.
- 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.
- Click on the Columns Tab to configure Mappings. Click OK to save.
- You can see the requests in Fiddler.
- And that’s it. Execute your package to Load JIRA data to SQL Server.
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.
Finally, you can use the following links for more information about the use of JIRA API with our tools: