Jira Connector
Documentation
Version: 11
Documentation
Endpoint

Read Issues


Name

get_issues

Description

No description available

Related Tables

Issues

Parameters

Parameter Label Required Options Description
SearchBy Search by NO
Option Value
None
Project Key(s) Project
JQL Expression JQL
Issue Id(s) or Key(s) Key
Project Project NO Specify a project to search issues for
JQL JQL NO
Option Value
Search Ids (IN) key IN(10001, 10002, 10003)
Search Keys (IN) key IN(CS-1, CS-2, CS-3)
Search Projects (IN) project IN(PROJ1, PROJ2, PROJ3)
Search Status (EQUAL) status='Done'
Search Date (Expression 1) created >=-5d
Search Date (Expression 2) created >=startOfMonth() AND created <=now()
Search Date (Expression 3) created >=startOfYear() AND created <=startOfDay()
Search Date (Static) created >= "2008-12-31"
Search Date (Static with time) created >= '2008/12/31 23:59'
Search Date (Issue Update Date) updated >=-5d
Search Project and Status (AND + IN) project=CS AND status NOT IN ('Done', 'Open', 'Closed')
Search Assignee and Created Date assignee is NOT EMPTY and created < -1d
Search Text (Contains - Fuzzy) Summary ~ 'some words' OR description ~ 'some words'
Search Text (Contains - Fuzzy Wildcard) Summary ~ 'some*' OR description ~ 'some*'
Search Text (Contains - Exact) Summary ~ '\"exact words\"' OR description ~ '\"exact words\"'
Search Text (Does Not Contain - Fuzzy) Summary !~ 'some words' OR description !~ 'some words'
Search Empty OR Null fixVersion is empty OR fixVersion is null
Search Is Not Empty OR Is Not Null fixVersion is not empty OR fixVersion is not null
Search WAS Operator (previous value) status WAS "Resolved" BY (jsmith,srogen) BEFORE "2019/02/02"
Search WAS IN Operator status WAS IN ("Resolved", "In Progress")
Search WAS NOT IN Operator status WAS NOT IN ("Resolved", "In Progress")
Search WAS + BY + DURING (date range) status WAS "Resolved" BY (jsmith,srogen) DURING("2019/02/02", "2020/02/02")
Search CHANGED operator assignee CHANGED
Search CHANGED operator (multiple) status CHANGED FROM "In Progress" TO "Open"
Use JQL to use an advanced mode of searching. Few links are https://www.atlassian.com/blog/jira/jql-the-most-flexible-way-to-search-jira-14 and als check https://wac-cdn.atlassian.com/dam/jcr:241d9c9b-475f-411f-90a2-97ca344442b3/atlassian-jql-cheat-sheet-2.pdf?cdnVersion=2169 [API reference]
Key Issue Key(s) or Ids - Comma Separated (PRJA-10,PRJA-13) NO Use comma separated IDs or Keys for Issues. 1000 ids max or JIRA internal limit for Payload.
CustomColumnsRegex CustomColumnsRegex NO Enter regular expression for custom columns which you like to output. Use Pipe to type multiple column names (must use key - internal names for columns e.g. customfield_10004). You can use Regex too (e.g. field_100\d+ ). This filter is only applied for those fields which has flag custom=true.

Output Columns

Label Data Type (SSIS) Data Type (SQL) Length Raw Description
Id DT_I8 bigint False
Key DT_WSTR nvarchar(50) 50 False
ProjectKey DT_WSTR nvarchar(50) 50 False
ProjectName DT_WSTR nvarchar(500) 500 False
StatusName DT_WSTR nvarchar(500) 500 False
Summary DT_WSTR nvarchar(2000) 2000 False
Created DT_DBTIMESTAMP datetime False
Updated DT_DBTIMESTAMP datetime False
Description DT_NTEXT nvarchar(MAX) False Join formatted text blocks using space separator (new lines will be lost)
DescriptionFormatted DT_NTEXT nvarchar(MAX) False
CreatorAccountId DT_WSTR nvarchar(100) 100 False
CreatorEmailAddress DT_WSTR nvarchar(100) 100 False
ReporterAccountId DT_WSTR nvarchar(100) 100 False
ReporterEmailAddress DT_WSTR nvarchar(100) 100 False
AssigneeAccountId DT_WSTR nvarchar(100) 100 False
AssigneeEmailAddress DT_WSTR nvarchar(100) 100 False
ResolutionId DT_I8 bigint False
ResolutionDescription DT_WSTR nvarchar(4000) 4000 False
ResolutionName DT_WSTR nvarchar(500) 500 False
Resolution DT_NTEXT nvarchar(MAX) False
TimespentInSeconds DT_I8 bigint False
DueDate DT_DBDATE date False
Expand DT_WSTR nvarchar(150) 150 False
StatusCategoryChangeDate DT_DBTIMESTAMP datetime False
Self DT_WSTR nvarchar(150) 150 False
IssueTypeSelf DT_WSTR nvarchar(150) 150 False
IssueTypeId DT_I8 bigint False
IssueTypeDescription DT_NTEXT nvarchar(MAX) False
IssueTypeIconUrl DT_WSTR nvarchar(500) 500 False
IssueTypeName DT_WSTR nvarchar(100) 100 False
IssueTypeSubtask DT_BOOL bit False
IssueTypeAvatarId DT_I4 int False
IssueTypeEntityId DT_GUID uniqueidentifier False
IssueTypeHierarchyLevel DT_I4 int False
ProjectSelf DT_WSTR nvarchar(150) 150 False
ProjectId DT_I8 bigint False
ProjectTypeKey DT_WSTR nvarchar(100) 100 False
ProjectSimplified DT_BOOL bit False
ProjectAvatarUrls48x48 DT_WSTR nvarchar(500) 500 False
ProjectAvatarUrls24x24 DT_WSTR nvarchar(500) 500 False
ProjectAvatarUrls16x16 DT_WSTR nvarchar(500) 500 False
ProjectAvatarUrls32x32 DT_WSTR nvarchar(500) 500 False
FixVersions DT_NTEXT nvarchar(MAX) False
AggregateTimespentInSeconds DT_I8 bigint False
ResolutionDate DT_DBTIMESTAMP datetime False
WorkRatio DT_WSTR nvarchar(100) 100 False
WatchesSelf DT_WSTR nvarchar(150) 150 False
WatchesWatchCount DT_I4 int False
WatchesIsWatching DT_BOOL bit False
LastViewed DT_DBTIMESTAMP datetime False
PrioritySelf DT_WSTR nvarchar(150) 150 False
PriorityIconUrl DT_WSTR nvarchar(500) 500 False
PriorityName DT_WSTR nvarchar(100) 100 False
PriorityId DT_I4 int False
Labels DT_NTEXT nvarchar(MAX) False
AggregateTimeOriginalEstimate DT_WSTR nvarchar(50) 50 False
TimeEstimate DT_WSTR nvarchar(50) 50 False
Versions DT_NTEXT nvarchar(MAX) False
IssueLinks DT_NTEXT nvarchar(MAX) False
AssigneeSelf DT_WSTR nvarchar(150) 150 False
AssigneeAvatarUrls48x48 DT_WSTR nvarchar(500) 500 False
AssigneeAvatarUrls24x24 DT_WSTR nvarchar(500) 500 False
AssigneeAvatarUrls16x16 DT_WSTR nvarchar(500) 500 False
AssigneeAvatarUrls32x32 DT_WSTR nvarchar(500) 500 False
AssigneeDisplayName DT_WSTR nvarchar(500) 500 False
AssigneeActive DT_BOOL bit 32 False
AssigneeTimeZone DT_WSTR nvarchar(100) 100 False
AssigneeAccountType DT_WSTR nvarchar(100) 100 False
StatusSelf DT_WSTR nvarchar(150) 150 False
StatusDescription DT_NTEXT nvarchar(MAX) False
StatusIconUrl DT_WSTR nvarchar(500) 500 False
StatusId DT_I4 int False
StatusCategorySelf DT_WSTR nvarchar(150) 150 False
StatusCategoryId DT_I4 int False
StatusCategoryKey DT_WSTR nvarchar(100) 100 False
StatusCategoryColorName DT_WSTR nvarchar(100) 100 False
StatusCategoryName DT_WSTR nvarchar(100) 100 False
Components DT_NTEXT nvarchar(MAX) False
TimeOriginalEstimate DT_WSTR nvarchar(50) 50 False
DescriptionVersion DT_I4 int False
DescriptionType DT_WSTR nvarchar(50) 50 False
Security DT_WSTR nvarchar(150) 150 False
AggregateTimeEstimate DT_I8 bigint False
CreatorSelf DT_WSTR nvarchar(150) 150 False
CreatorAvatarUrls48x48 DT_WSTR nvarchar(500) 500 False
CreatorAvatarUrls24x24 DT_WSTR nvarchar(500) 500 False
CreatorAvatarUrls16x16 DT_WSTR nvarchar(500) 500 False
CreatorAvatarUrls32x32 DT_WSTR nvarchar(500) 500 False
CreatorDisplayName DT_WSTR nvarchar(200) 200 False
CreatorActive DT_BOOL bit False
CreatorTimeZone DT_WSTR nvarchar(100) 100 False
CreatorAccountType DT_WSTR nvarchar(100) 100 False
Subtasks DT_NTEXT nvarchar(MAX) False
ReporterSelf DT_WSTR nvarchar(150) 150 False
ReporterAvatarUrls48x48 DT_WSTR nvarchar(500) 500 False
ReporterAvatarUrls24x24 DT_WSTR nvarchar(500) 500 False
ReporterAvatarUrls16x16 DT_WSTR nvarchar(500) 500 False
ReporterAvatarUrls32x32 DT_WSTR nvarchar(500) 500 False
ReporterDisplayName DT_WSTR nvarchar(100) 100 False
ReporterActive DT_BOOL bit False
ReporterTimeZone DT_WSTR nvarchar(100) 100 False
ReporterAccountType DT_WSTR nvarchar(100) 100 False
AggregateProgressPercent DT_I4 int False
AggregateProgressInSeconds DT_I8 bigint False
AggregateProgressTotalInSeconds DT_I8 bigint False
Environment DT_NTEXT nvarchar(MAX) False Join formatted text blocks using space separator (new lines will be lost)
EnvironmentFormatted DT_NTEXT nvarchar(MAX) False
ProgressInSeconds DT_I8 bigint False
ProgressTotalInSeconds DT_I8 bigint False
VotesSelf DT_WSTR nvarchar(150) 150 False
Votes DT_I4 int False
HasVoted DT_BOOL bit False
ParentId DT_I4 int False
ParentKey DT_WSTR nvarchar(50) 50 False
ParentSelf DT_WSTR nvarchar(150) 150 False
ParentSummary DT_WSTR nvarchar(2000) 2000 False
ParentStatusSelf DT_WSTR nvarchar(150) 150 False
ParentStatusDescription DT_WSTR nvarchar(2000) 2000 False
ParentStatusIconUrl DT_WSTR nvarchar(500) 500 False
ParentStatusName DT_WSTR nvarchar(100) 100 False
ParentStatusId DT_I8 bigint False
ParentStatusCategorySelf DT_WSTR nvarchar(150) 150 False
ParentStatusCategoryId DT_I4 int False
ParentStatusCategoryKey DT_WSTR nvarchar(50) 50 False
ParentStatusCategoryColorName DT_WSTR nvarchar(50) 50 False
ParentStatusCategoryName DT_WSTR nvarchar(50) 50 False
ParentprioritySelf DT_WSTR nvarchar(150) 150 False
ParentpriorityIconUrl DT_WSTR nvarchar(500) 500 False
ParentpriorityName DT_WSTR nvarchar(50) 50 False
ParentpriorityId DT_I4 int False
ParentIssueTypeSelf DT_WSTR nvarchar(150) 150 False
ParentIssueTypeId DT_I8 bigint False
ParentIssueTypeDescription DT_WSTR nvarchar(2000) 2000 False
ParentIssueTypeIconUrl DT_WSTR nvarchar(500) 500 False
ParentIssueTypeName DT_WSTR nvarchar(50) 50 False
ParentIssueTypeSubtask DT_BOOL bit False
ParentIssueTypeAvatarId DT_I4 int False
ParentIssueTypeEntityId DT_GUID uniqueidentifier False
ParentIssueTypeHierarchyLevel DT_I4 int False
Assignee DT_WSTR nvarchar(150) 150 False
ResolutionSelf DT_WSTR nvarchar(150) 150 False
[$parent.key$] DT_WSTR nvarchar(2000) 2000 False [Dynamic Column]
[$parent.key$]_value DT_WSTR nvarchar(2000) 2000 False [Dynamic Column]_value
[$parent.key$]_id DT_WSTR nvarchar(20) 20 False [Dynamic Column]_id
[$parent.key$]_values DT_WSTR nvarchar(2000) 2000 False [Dynamic Column]_values
[$parent.key$]_text DT_NTEXT nvarchar(MAX) False [Dynamic Column]_text Join formatted text blocks using space separator (new lines will be lost)
[$parent.key$]_accountId DT_WSTR nvarchar(100) 100 False [Dynamic Column]_accountId
[$parent.key$]_email DT_WSTR nvarchar(300) 300 False [Dynamic Column]_email
[$parent.key$]_name DT_WSTR nvarchar(300) 300 False [Dynamic Column]_name
If the column you are looking for is missing, consider customizing Jira Connector.

Input Columns

Label Data Type (SSIS) Data Type (SQL) Length Raw Description
There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime.

Examples

SSIS

Use Jira Connector in API Source component to read data or in API Destination component to read/write data:

Read from Issues table using API Destination

This Endpoint belongs to Issues table, therefore you cannot work with it directly. Use this table and table-operation pair instead:

Jira
Issues
Select, Lookup
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Read issues

SELECT * FROM get_issues

List issues

Lists all issues

SELECT * FROM Issues

List a single issue by Id

List a single issue by Key (e.g. CS-123) or Numeric Id (e.g. 10001).

SELECT * FROM Issues WITH(SearchBy='Key', Key='10001')

List a single issue by Id - Continue on a specific error message

By default if issue is not found or search condition is bad you may get an error but you can continue by setting ContineOnErrorForMessage=1 and message you like to ignore in ErrorSubstringToMatch.

SELECT * FROM Issues WITH(SearchBy='Key', Key='10001', ContineOnErrorForMessage=1, ErrorSubstringToMatch='Issue does not exist')

List a single issue by Key

List a single issue by Key (e.g. CS-123) or Numeric Id (e.g. 10001).

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

List multiple issues by Id or Key

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

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

List all issues for a specific project

List all issues for a specified project code.

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

Search issues using Advanced JQL query expression

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"

*/

get_issues endpoint belongs to Issues table(s), and can therefore be used via those table(s).

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Read issues

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_issues';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

List issues

Lists all issues

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

List a single issue by Id

List a single issue by Key (e.g. CS-123) or Numeric Id (e.g. 10001).

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''10001'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

List a single issue by Id - Continue on a specific error message

By default if issue is not found or search condition is bad you may get an error but you can continue by setting ContineOnErrorForMessage=1 and message you like to ignore in ErrorSubstringToMatch.

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''10001'', ContineOnErrorForMessage=1, ErrorSubstringToMatch=''Issue does not exist'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

List a single issue by Key

List a single issue by Key (e.g. CS-123) or Numeric Id (e.g. 10001).

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''CS-1'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

List multiple issues by Id or Key

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

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''CS-1, CS-2, 10003, 10004'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

List all issues for a specific project

List all issues for a specified project code.

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH(Project=''CS'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

Search issues using Advanced JQL query expression

List issues using JQL query expression

DECLARE @MyQuery NVARCHAR(MAX) = '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"

*/';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

get_issues endpoint belongs to Issues table(s), and can therefore be used via those table(s).