Jira Connector

ZappySys Jira Connector provide read / write capability inside your app (see list below), using these drag and drop , high performance connector you can perform many Jira operations without any coding. You can use this connector to integrate Jira data inside apps like SSIS, SQL Server or popular ETL Platforms / BI Tools/ Reporting Apps / Programming languages (i.e. Informatica, Power BI, SSRS, Excel, C#, JAVA, Python)

Download for SSIS Download for Other Apps Documentation

Click on your App below to get started with Jira Integration

Choose your App / Scenario for which you like to integrate Jira

Actions supported by Jira Connector

Jira Connector support following actions for REST API integration. If some actions are not listed below then you can easily edit Connector file and enhance out of the box functionality.
 Read Resources
   [Read more...]
 Read Fields
   [Read more...]
Parameter Description
 Read Custom Fields
   [Read more...]
Parameter Description
CustomColumnsRegex Enter regular expression for custom columns which you like to output. Use Pipe to type keys (internal names for columns). You can use Regex too (e.g. field_100\d+ )
 Read Issue Types
   [Read more...]
 Read Users
   [Read more...]
Parameter Description
 Create User
   [Read more...]
 Delete User
   [Read more...]
Parameter Description
AccountId
 Read Projects
   [Read more...]
 Create Project
   [Read more...]
 Upsert Project
   [Read more...]
Parameter Description
ProjectIdOrKey
 Delete Project
   [Read more...]
Parameter Description
ProjectIdOrKey Project ID or Key, uniquely representing a project
EnableUndo
 Read Issues
   [Read more...]
 Create Issues
   [Read more...]
 Update Issue
   [Read more...]
Parameter Description
IssueIdOrKey Enter Issue ID or Key
NotifyUsers Whether a notification email about the issue update is sent to all watchers. Possible values: true, false
Option Value
true true
false false
OverrideScreenSecurity Whether screen security should be overridden to enable hidden fields to be edited. Possible values: true, false
Option Value
true true
false false
OverrideEditableFlag Whether screen security should be overridden to enable uneditable fields to be edited. Possible values: true, false
Option Value
true true
false false
 Delete Issue
   [Read more...]
Parameter Description
IssueIdOrKey Enter Issue ID or Key
 Read Worklogs
   [Read more...]
Parameter Description
Issue Key(s) or Ids - Comma Separated (PRJA-10,PRJA-13) Use comma separated IDs or Keys for Issues. 1000 ids max or JIRA internal limit for Payload.
 Create Worklog
   [Read more...]
 Update Worklog
   [Read more...]
Parameter Description
IssueIdOrKey
WorklogId
 Delete Worklog
   [Read more...]
Parameter Description
IssueIdOrKey
WorklogId
 Read Comments
   [Read more...]
Parameter Description
Issue Key(s) or Ids - Comma Separated (PRJA-10,PRJA-13) Use comma separated IDs or Keys for Issues. 1000 ids max or JIRA internal limit for Payload.
 Read Changelogs
Returns a paginated list of all changelogs for an issue sorted by date, starting from the oldest. OAuth scopes required: read:issue-meta:jira, read:avatar:jira, read:issue.changelog:jira    [Read more...]
Parameter Description
IssueId Or Key
 Read Changelog Details
Returns a paginated list of all changelogs and fields updated for an issue sorted by date, starting from the oldest. OAuth scopes required: read:issue-meta:jira, read:avatar:jira, read:issue.changelog:jira    [Read more...]
Parameter Description
IssueId Or Key
 Read Changelogs by IDs
Returns changelogs for an issue specified by a list of changelog IDs. OAuth scopes required: read:issue-meta:jira, read:avatar:jira, read:issue.changelog:jira    [Read more...]
Parameter Description
IssueId Or Key
Comma Separated ChangeLog Ids
 Get custom field contexts
Returns a paginated list list of contexts for a custom field. OAuth scopes required: read:field:jira, read:custom-field-contextual-configuration:jira    [Read more...]
Parameter Description
FieldId
 Get custom field context options
Returns a paginated list of all custom field option for a context. Options are returned first then cascading options, in the order they display in Jira. OAuth scopes required: read:field.option:jira    [Read more...]
Parameter Description
FieldId
ContextId
 Generic Request
This is generic endpoint. Use this endpoint when some actions are not implemented by connector. Just enter partial URL (Required), Body, Method, Header etc. Most parameters are optional except URL.    [Read more...]
Parameter Description
Url API URL goes here. You can enter full URL or Partial URL relative to Base URL. If it is full URL then domain name must be part of ServiceURL or part of TrustedDomains
Body Request Body content goes here
IsMultiPart Set this option if you want to upload file(s) (i.e. POST RAW file data) or send data using Multi-Part encoding method (i.e. Content-Type: multipart/form-data). Multi-Part request allows you to mix key/value and upload files in same request. On the other hand raw upload allows only single file upload (without any key/value) ==== Raw Upload (Content-Type: application/octet-stream) ===== To upload single file in raw mode check this option and specify full file path starting with @ sign in the Body (e.g. @c:\data\myfile.zip ) ==== Form-Data / Multipart Upload (Content-Type: multipart/form-data) ===== To treat your Request data as multi part fields you must specify key/value pairs separated by new lines into RequestData field (i.e. Body). Each key value pair is entered on new-line and key/value are separated using equal sign (=). Preceding and trailing spaces are ignored also blank lines are ignored. If field value has some any special character(s) then use escape sequence (e.g. For NewLine: \r\n, For Tab: \t, For at (@): \@). When value of any field starts with at sign (@) its automatically treated as File you want to upload. By default file content type is determined based on extension however you can supply content type manually for any field using this way [ YourFileFieldName.Content-Type=some-content-type ]. By default File Upload Field always includes Content-Type in the request (non file fields do not have content-type by default unless you supply manually). For some reason if you dont want to use Content-Type header in your request then supply blank Content-Type to exclude this header altogather [e.g. SomeFieldName.Content-Type= ]. In below example we have supplied Content-Type for file2 and SomeField1, all other fields are using default content-type. See below Example of uploading multiple files along with additional fields. If some API requires you to pass Content-Type: multipart/form-data rather than multipart/form-data then manually set Request Header => Content-Type: multipart/mixed (it must starts with multipart/ else will be ignored). file1=@c:\data\Myfile1.txt file2=@c:\data\Myfile2.json file2.Content-Type=application/json SomeField1=aaaaaaa SomeField1.Content-Type=text/plain SomeField2=12345 SomeFieldWithNewLineAndTab=This is line1\r\nThis is line2\r\nThis is \ttab \ttab \ttab SomeFieldStartingWithAtSign=\@MyTwitterHandle
Filter Enter filter to extract array from response. Example: $.rows[*] --OR-- $.customers[*].orders[*]. Check your response document and find out hierarchy you like to extract
Headers Headers for Request. To enter multiple headers use double pipe or new line after each {header-name}:{value} pair

Jira Connector Examples (For ODBC PowerPack)

This page offers a collection of SQL examples designed for seamless integration with the ZappySys API ODBC Driver under ODBC Data Source (36/64) or ZappySys Data Gateway, enhancing your ability to connect and interact with Prebuilt Connectors effectively.

List issues    [Read more...]

Lists all issues

SELECT * FROM Issues

List a single issue by Id    [Read more...]

select * from Issues Where Id=10001

List a single issue by Key    [Read more...]

List a single issue by Key or Numeric Id.

SELECT * FROM Issues WITH(Key='CS-1')

List multiple issues by Id or Key    [Read more...]

List multiple issues by comma separated Key(s) or Numeric Id(s).

SELECT * FROM Issues WITH(Key='CS-1, CS-2, 10003, 10004')

List all issues for a specific project    [Read more...]

List all issues for a specified project code.

SELECT * FROM Issues WITH(Project='CS')

Search issues using Advanced JQL query expression    [Read more...]

List issues using JQL query expression

SELECT * FROM Issues WITH (Jql='status IN (Done, Closed) AND created > -5d' )
/*
Useful links:
  https://support.atlassian.com/jira-work-management/docs/jql-fields/
  https://www.atlassian.com/software/jira/guides/jql/tutorials#advanced-search
  https://www.atlassian.com/blog/jira/jql-the-most-flexible-way-to-search-jira-14
  
Other Possible JQL expressions:

Ids (IN):       key IN(10001, 10002, 10003);
Keys (IN):       key IN(CS-1, CS-2, CS-3);
Projects (IN):       project IN(PROJ1, PROJ2, PROJ3);
Status (EQUAL):       status='Done';
Date (Expression 1):       created >=-5d;
Date (Expression 2):       created >=startOfMonth() AND created <=now();
Date (Expression 3):       created >=startOfYear() AND created <=startOfDay();
Date (Static):       created >= 2008/12/31;
Date (Static with time):       created >= 2008/12/31 23:59;
Project and Status (AND + IN):       project=CS AND status NOT IN ('Done', 'Open', 'Closed');
Assignee and Created Date:    assignee is NOT EMPTY and created < -1d;
Text (Contains - Fuzzy):       Summary ~ 'some words' OR description ~ 'some words';
Text (Contains - Fuzzy Wildcard):       Summary ~ 'some*' OR description ~ 'some*';
Text (Contains - Exact):       Summary ~ '\"exact words\"' OR description ~ '\"exact words\"';
Text (Does Not Contain - Fuzzy):       Summary !~ 'some words' OR description !~ 'some words';
Empty OR Null:   fixVersion is empty OR fixVersion is null;
Is Not Empty OR Is Not Null:    fixVersion is not empty OR fixVersion is not null;
WAS Operator (previous value):       status WAS "Resolved" BY (jsmith,srogen) BEFORE "2019/02/02";
WAS IN Operator:   status WAS IN ("Resolved", "In Progress");
WAS NOT IN Operator:    status WAS NOT IN ("Resolved", "In Progress");
WAS + BY + DURING (date range):       status WAS "Resolved" BY (jsmith,srogen) DURING("2019/02/02", "2020/02/02");
CHANGED operator:    assignee CHANGED;
CHANGED operator (multiple):       status CHANGED FROM "In Progress" TO "Open"

*/

INSERT Issue    [Read more...]

Inserts a single issue to a particular project

INSERT INTO Issues(ProjectKey, IssueTypeName, Summary, Description)
VALUES('SMP', 'Task', 'My ticket inserted through the API', 'A description about an issue')
WITH (Output=1)

UPDATE Issue    [Read more...]

Updates an issue

UPDATE Issues
SET Summary = 'This is my summary'
   ,Description = 'Lot''s of stuff to describe'
   ,Labels = '[ "bugfix" ]'
   ,DueDate = '2029-10-10'
--WHERE Id=1234   
--WHERE Id='ISSKEY'   
WITH (
	IssueIdOrKey='ISSKEY', --or use Id in where clause 
	Output=1,
	NotifyUsers=0,
	OverrideScreenSecurity=0,
	OverrideEditableFlag=0,
	ContinueOn404Error=0
)'

UPDATE multiple Issues matching with JQL search query    [Read more...]

Updates multiple issues which match with certain condition (JQL). Adding Where 1=1 or some other WHERE condition invokes Lookup endpoint (refer to other example to learn about JQL - see Search issues using Advanced JQL query expression)

UPDATE Issues
SET Summary = 'This is done on <<fun_now>>'
Where 1=1
WITH (jql='status=done' , ContinueOn404Error=0)
--WITH (jql='key in(10001, 10002, 10003)' , ContinueOn404Error=0)
--WITH (jql='key in(CS-1, CS-2, CS-3)', ContinueOn404Error=0)

DELETE Issue    [Read more...]

Deletes a single issue

DELETE FROM Issues
WITH (IssueIdOrKey='10020', OUTPUT=1, ContinueOn404Error=0)

DELETE multiple Issues matching with JQL search query    [Read more...]

Delete multiple issues which match with certain condition (JQL). Adding Where 1=1 or some other WHERE condition invokes Lookup endpoint (refer to other example to learn about JQL - see Search issues using Advanced JQL query expression)

DELETE FROM Issues
Where 1=1
WITH (jql='status=done' , ContinueOn404Error=0)
--WITH (jql='key in(10001, 10002, 10003)' , ContinueOn404Error=0)
--WITH (jql='key in(CS-1, CS-2, CS-3)', ContinueOn404Error=0)

List comments for all issues    [Read more...]

List comments for all issues

SELECT * FROM Comments

List comments for a specific issue Issue Key or Id    [Read more...]

List comments for a specific issue

SELECT * FROM Comments Where IssueId=10003 --OR WITH(Key='10003')

List comments for a specific issue by Key    [Read more...]

List comments for a specific issue by key

SELECT * FROM Comments WITH(Key='CS-1')

List comments for issues (search by JQL)    [Read more...]

List all comments for issues returned from a JQL search expression (refer to previous example to learn about JQL - see Search issues using Advanced JQL query expression)

SELECT * FROM Comments WITH (Jql='status IN (Done, Closed) AND created > -5d' )

List fields    [Read more...]

Lists all fields that are used and available in issue entity

SELECT * FROM Fields

List projects    [Read more...]

Lists all available projects

SELECT * FROM Projects

List worklog entries for a specific issue by Id or Key    [Read more...]

List all worklog entries for a specific issue

SELECT * FROM Worklogs Where IssueId=10003 --WITH(Key='10003')

List worklogs for issues (search by JQL)    [Read more...]

List all worklogs (time entries) for issues returned from a JQL search expression (refer to previous example to learn about JQL - see Search issues using Advanced JQL query expression)

SELECT * FROM Worklogs WITH (Jql='status IN (Done, Closed) AND created > -5d' )

INSERT Project    [Read more...]

Inserts a single project

INSERT INTO Projects(ProjectKey, Name, ProjectTypeKey, LeadAccountId, AssigneeType)
VALUES ('TEST', 'Test Project', 'software', '70122:XXXXXXXX-XXXX-XXXX-XXXX-c5da8c98b9e2', 'PROJECT_LEAD)
WITH (Output=1)')

UPDATE Project    [Read more...]

Updates a single project

UPDATE Projects
SET Name = 'My Test Kanban Project'
   ,ProjectCategoryId = 1
WITH (ProjectIdOrKey = 'MYPRJCT', Output=1, ContinueOn404Error=0)

DELETE Project    [Read more...]

Deletes a single project

DELETE FROM Projects
WITH (ProjectIdOrKey = '10020', Output=1, ContinueOn404Error=0)

List users    [Read more...]

Lists all available users

SELECT * FROM Users

INSERT User    [Read more...]

Inserts a single user

INSERT INTO Users(EmailAddress, DisplayName, Name, Password)
VALUES ('my@user.com', 'John Doe', 'John', 'xhedkspstdadaothoua')
WITH (OUTPUT=1)

DELETE User    [Read more...]

Deletes a single user

DELETE FROM Users
WITH (OUTPUT=1, accountId = '547059:136095a0-XXXX-XXXX-XXXX-3e4c66f26551', ContinueOn404Error=0)

List worklogs    [Read more...]

Lists all worklogs from all issues

SELECT * FROM Worklogs

INSERT Worklog    [Read more...]

Inserts a single worklog to a particular issue

INSERT INTO Worklogs(TimeSpentInSeconds, Comment, StartedAt)
      VALUES(7200,'My Comment!','2020-02-23T16:20:30.123+0000')
      WITH (IssueIdOrKey='ISSKEY-1', OUTPUT=1)

UPDATE Worklog    [Read more...]

Updates a worklog

UPDATE Worklogs
SET TimeSpentInSeconds = 28800
   ,Comment='My Comment!'
   ,StartedAt='2020-01-23T16:20:30.123+0000'
WITH (IssueIdOrKey='MTK-1', WorklogId='123465', OUTPUT=1, ContinueOn404Error=0)

DELETE Worklog    [Read more...]

Deletes a single worklog of an issue

DELETE FROM Worklogs
WITH (IssueIdOrKey='10020', WorklogId='123465', OUTPUT=1, ContinueOn404Error=0)

Download for SSIS Download for Other Apps