Insert, Update and Delete JIRA Issue in SQL Server

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

In our previous blog we saw how to read JIRA data in SQL Server. In this blog, we will learn how to Insert, Update and Delete Issue in JIRA using ZappySys JSON Driver. We will also read data from SQL Server data table in stored-procedure and insert JIRA issues in bulk, along with few other topics such as how to generate an API Token using Atlassian Account for JIRA REST API Call, how to update and delete Issue data in JIRA using REST API calls.

So, let’s get started.

Requirements

A first requirement will be to SQL Server Database Engine Installed
The second requirement will be SSMS installed
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, Insert issue in JIRA using static data from the SQL server using ODBC JSON driver REST API Call. Update and Delete JIRA issue data From the SQL Server. Insert Bulk JIRA issues from the stored procedure. 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

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 inserting issue in JIRA we will use JIRA REST API create issue method. We can also insert issues in bulk using Create issues bulk method. To update issue we will use this PUT method: Updating an Issue via the JIRA REST APIs. To delete issue we will use this DELETE method. You can find JIRA Rest API example on this link: Jira REST API examples

Step-by-Step: Write in JIRA from SQL Server

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:

https://YOUR-DOMAIN.atlassian.net/rest/api/2/issue/

1	https://YOUR-DOMAIN.atlassian.net/rest/api/2/issue/

hence, your-domain is your Atlassian Cloud account’s domain name (we are using zappysys2.atlassian.net).

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 REST API reference)

Configure ZappySys Gateway data Source for JIRA

To do this, first of all, we will open the ZappySys Data Gateway Configuration:
Open ZappySys Data Gateway
Add the Native – ZappySys JSON Driver Data source.
Add Gateway Data Source (Native JSON Driver)
Now it’s time to connect with JIRA. Let’s use JSON Driver Data source with the following URL:

https://YOUR-DOMAIN.atlassian.net/rest/api/2/issue/

1

https://YOUR-DOMAIN.atlassian.net/rest/api/2/issue/

ODBC JSON Driver: Enter Request URL
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

Now, Let’s Add request body input data of issue.

{
    "fields": {
       "project":
       {
          "id": "<YOUR-PROJECT-ID>"
       },
       "summary": "No REST for the Wicked.",
       "description": "Creating of an issue using IDs for projects and issue types using the REST API",
       "issuetype": {
          "id": "<ISSUE-TYPE-ID>"
       }
   }
}

{

"fields": {

"project":

{

"id": "<YOUR-PROJECT-ID>"

"summary": "No REST for the Wicked.",

"description": "Creating of an issue using IDs for projects and issue types using the REST API",

"issuetype": {

"id": "<ISSUE-TYPE-ID>"

}

Issue Input Data

Now using Query Builder we will generate the query.
Generate JIRA Rest API Post Method Query
Now Let’s Test Connection and it will prompt the message “Connection is OK!”
That’s it we have successfully configured JIRA Rest API in ODBC JSON Driver.

Setup Linked Server in MS SQL Server for JIRA

Once you configured the data source in Gateway, we can now setup Linked Server in SQL Server to query API Calls.

Assuming you have installed SQL Server and SSMS. If not then get both for FREE from here: Get SQL Server Express and Get SSMS
Open SSMS and connect to SQL Server.
Go to Root > Server Objects > Linked Servers node. Right click and click New Linked Server…

Add Linked Server in SQL Server
Now enter the linked server name, select Provider as SQL Native Client
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.
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
Click on Security Tab and select last option “Be made using this security context“. Enter your gateway user account here.
Click OK to save Linked Server

Insert data in JIRA from SQL Server (Call POST API Method)

To Insert the issue in JIRA we need to execute the query like this. Please refer to this link for more information: Creating an issue example.

Important Note*: If you don’t pass META='{xxxx}’ in query driver call REST API 2 times first time to parse metadata and second time to get actual data. So that you find some odd thing in JIRA project if you execute the query without Meta then you will find two same issues created in your JIRA project as the driver makes two calls as mentioned above.

SELECT * FROM OPENQUERY([MY_LINKED_SERVER_NAME], 
'SELECT * FROM $
WITH( META=''[{"Name": "Success","Type": "Int32"}]''
     ,Src=''https://zappysys2.atlassian.net/rest/api/2/issue/''
     ,RequestData=''{
       "fields": {
        "project":
        {
          "id": "10110"
        },
        "summary": "No REST for the Wicked.",
        "description": "Creating of an issue using IDs for projects and issue types using the REST API",
        "issuetype": {
          "id": "1"
       }
     }
}''
,Header=''Content-Type: application/json || Accept: */*''
,RequestMethod=''POST''
)')

SELECT * FROM OPENQUERY([MY_LINKED_SERVER_NAME],

'SELECT * FROM $

WITH( META=''[{"Name": "Success","Type": "Int32"}]''

,Src=''https://zappysys2.atlassian.net/rest/api/2/issue/''

,RequestData=''{

"fields": {

"project":

{

"id": "10110"

"summary": "No REST for the Wicked.",

"description": "Creating of an issue using IDs for projects and issue types using the REST API",

"issuetype": {

"id": "1"

}

}''

,Header=''Content-Type: application/json || Accept: */*''

,RequestMethod=''POST''

)')

Here is the preview after you run REST API query in SQL Server. It will return created issue id, key, and Link. Now let’s note down this response to update and delete the same issue if you want to. Notice that you can override default configuration by supplying many parameters in WITH clause.
Insert Issue In JIRA Using SQL Query
You can also find this created issue in your JIRA project.
Created Issue in JIRA

Update data in JIRA from SQL Server (Call PUT API Method)

Let’s update the issue which we created in the previous step using id. I Added Extra Meta details in Query to prevent driver to make two calls and also to prevent error as JIRA Rest API Response is a blank Status code of "204 No Content". Please refer to this link for more information: Updating an Issue via the JIRA REST APIs

SELECT * FROM OPENQUERY([MY_LINKED_SERVER_NAME]
, 'SELECT * FROM $
   WITH(META=''[{"Name": "Success","Type": "Int32"}]'' ,
	 Src=''https://zappysys2.atlassian.net/rest/api/2/issue/10391''
	,DataConnectionType=''HTTP''
	,RequestData=''{
    "fields": {
       "summary": "Updated One!...Ye merry gentlemen."
   }
}''
	,Header=''Content-Type: application/json || Accept: */*''
	,RequestMethod=''PUT''
)')

SELECT * FROM OPENQUERY([MY_LINKED_SERVER_NAME]

, 'SELECT * FROM $

WITH(META=''[{"Name": "Success","Type": "Int32"}]'' ,

Src=''https://zappysys2.atlassian.net/rest/api/2/issue/10391''

,DataConnectionType=''HTTP''

,RequestData=''{

"fields": {

"summary": "Updated One!...Ye merry gentlemen."

}

}''

,Header=''Content-Type: application/json || Accept: */*''

,RequestMethod=''PUT''

)')

Let’s is the preview after you running update query in SQL Server.
Update Jira Issue From SQL Server
Now, Let’s see the updated issue in JIRA project.
Updated Issue In JIRA Project
One Import note if you don’t add that metadata then you will get an error like below as JIRA Rest API Response is a blank “Status code of ‘204 No Content'”. If you don’t set META the REST API Make Two calls First to parse metadata and for the second time to get actual data.

Query execution error: No data returned for specified table name or filter expression. Make sure table name or filter is typed correctly (it is case sensitive) and input data contains a corresponding element(s).

Delete data in JIRA from SQL Server (Call DELETE API Method)

Now let’s delete the issue which we created by executing following query in SQL Server. We also need to add extra meta details in query same as update one as JIRA Rest API Response is a blank Status code of "204 No Content". Please refer to this link for more information: Delete an Issue via the JIRA REST API

SELECT * FROM OPENQUERY([MY_LINKED_SERVER_NAME]
, 'SELECT * FROM $
   WITH(META=''[{"Name": "Success","Type": "Int32"}]'' ,
	 Src=''https://zappysys2.atlassian.net/rest/api/2/issue/10391''
	,DataConnectionType=''HTTP''
	,Header=''Content-Type: application/json || Accept: */*''
	,RequestMethod=''DELETE''
)')

SELECT * FROM OPENQUERY([MY_LINKED_SERVER_NAME]

, 'SELECT * FROM $

WITH(META=''[{"Name": "Success","Type": "Int32"}]'' ,

Src=''https://zappysys2.atlassian.net/rest/api/2/issue/10391''

,DataConnectionType=''HTTP''

,Header=''Content-Type: application/json || Accept: */*''

,RequestMethod=''DELETE''

)')

Now, Let’s check the deleted issue in JIRA project.
Issue Deleted in JIRA Project

Create multiple JIRA issues using SQL Server Stored Proc

Now lets put all previous things to more practical use. We will create a stored procedure which will read Issues stored in a SQL Table and Submit it to JIRA to create new Issue via REST API call. This will make it totally dynamic so SQL Table can act as your Source of information for API call.

Let’s create stored procedure to read data from “tblIssues” data table and create bulk issues in JIRA.

alter PROCEDURE usp_AddMultipleJiraIssues
AS
BEGIN

Declare @issueTemp nvarchar(2000)
Declare @PostQuery nvarchar(MAX)
Declare @strIssues nvarchar(MAX)
set @PostQuery='';
set @strIssues='';

---- Template 
set @issueTemp=	'{
			"fields": {
				"project": {
					"key": "<YOUR-JIRA-PROJECT-KEY>"
				},
				"summary": "[ISSUE-SUMMARY]",
				"description": "[ISSUE-DESCRIPTION]",
			        "issuetype": {
					  "id": "<YOUR-JIRA-ISSUE-TYPE>"
				}
			}
		}';

-----Generate issues JSON String from the data table 
Select @strIssues = @strIssues
                    + REPLACE(REPLACE(@issueTemp,'[ISSUE-SUMMARY]',isnull(i.summary,''))
                                             ,'[ISSUE-DESCRIPTION]',isnull(i.description,''))
                    + ',' 
from tblissues i

-----Trim last , comma from the string otherwise you will get an error 
SET @strIssues = LEFT(@strIssues,(LEN(@strIssues)-1))

-----JIRA Post Query
SET @PostQuery= 'SELECT * FROM OPENQUERY([UPDATE_JIRA_DATA_JSON_DRIVER]
					, ''SELECT * FROM $
					WITH(
						META=''''[{"Name": "Success","Type": "Int32"}]''''
                                                ,Src=''''https://zappysys2.atlassian.net/rest/api/2/issue/bulk''''
						,DataConnectionType=''''HTTP''''
						,RequestData=''''{
						   "issueUpdates": [' + @strIssues + ']
					        }''''
						,Header=''''Content-Type: application/json || Accept: */*''''
						,RequestMethod=''''POST''''
					)'')'

--Print(@PostQuery)

----Posting issues data in JIRA
Execute(@PostQuery)

END
GO

alter PROCEDURE usp_AddMultipleJiraIssues

BEGIN

Declare @issueTemp nvarchar(2000)

Declare @PostQuery nvarchar(MAX)

Declare @strIssues nvarchar(MAX)

set @PostQuery='';

set @strIssues='';

---- Template

set @issueTemp= '{

"fields": {

"project": {

"key": "<YOUR-JIRA-PROJECT-KEY>"

"summary": "[ISSUE-SUMMARY]",

"description": "[ISSUE-DESCRIPTION]",

"issuetype": {

"id": "<YOUR-JIRA-ISSUE-TYPE>"

}

}';

-----Generate issues JSON String from the data table

Select @strIssues = @strIssues

+ REPLACE(REPLACE(@issueTemp,'[ISSUE-SUMMARY]',isnull(i.summary,''))

,'[ISSUE-DESCRIPTION]',isnull(i.description,''))

+ ','

from tblissues i

-----Trim last , comma from the string otherwise you will get an error

SET @strIssues = LEFT(@strIssues,(LEN(@strIssues)-1))

-----JIRA Post Query

SET @PostQuery= 'SELECT * FROM OPENQUERY([UPDATE_JIRA_DATA_JSON_DRIVER]

, ''SELECT * FROM $

WITH(

META=''''[{"Name": "Success","Type": "Int32"}]''''

,Src=''''https://zappysys2.atlassian.net/rest/api/2/issue/bulk''''

,DataConnectionType=''''HTTP''''

,RequestData=''''{

"issueUpdates": [' + @strIssues + ']

}''''

,Header=''''Content-Type: application/json || Accept: */*''''

,RequestMethod=''''POST''''

)'')'

--Print(@PostQuery)

----Posting issues data in JIRA

Execute(@PostQuery)

END

That’s it now executes the stored procedure and you will find bulk issues created in your JIRA project.
Bulk Issues Created In Jira

Conclusion

In this blog, we learned how to generate a JIRA API Token and how to configure it, Insert, Update and delete Issue in JIRA using ODBC JSON / REST API Driver from the SQL server. You can achieve many more functionalities with this tool. Check our blogs/articles on 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:

JIRA Documentation for API
Landing Page for ODBC JSON / REST API Driver, you can also find Tutorial Video here.
Help File: Documentation of JSON Driver.
Blog/articles link: https://zappysys.com/blog/category/odbc-powerpack/odbc-drivers/json-rest-api-driver/