Table Issues

Description

No description available

Parameters

Parameter	Label	Required	Options	Description	Help
There are no parameters

Supported Operations

Below section contains supported CRUD operations. Each operation is executed by some EndPoint behind the scene.

Method	Supported	Reference EndPoint
SELECT		get_issues
INSERT		create_issues
UPDATE		update_issue
UPSERT
DELETE		delete_issue
LOOKUP		get_issues

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 Source

Jira

Issues

SSIS API Source - Read from table or endpoint

Read/write to Issues table using API Destination

Jira

Issues

Select

SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

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"

*/

INSERT Issue

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)

INSERT Multiple Issues (BULK Load from External System like Microsoft SQL Server / ODBC)

Inserts multiple issues from an external source (e.g. Microsoft SQL Server or ODBC)

INSERT INTO Issues(ProjectKey, IssueTypeName, Summary, Description)
SOURCE('MSSQL' --OR ODBC
, 'Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true'
--For ODBC
--, 'Dsn=MyOdbcDsn' 
--, 'Driver={My ODBC Driver Type}...' 
--Alias must match Supported Input Columns - Use Query Builder to find Input Column Names
,'select top 10 C_ProjKey as ProjectKey, C_Type as IssueTypeName, C_Summary as Summary, C_Desc as Description FROM Orders'

--Static value example
--,'select top 10 ''CS'' as ProjectKey, ''New Feature'' as IssueTypeName, ''Task created - <<FUN_SEQUENCE>>'' Summary, ''A description <<FUN_NOW>>'' Description FROM Orders'
)

UPDATE Issue

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 / Insert Issue with Custom Fields

Updates or Insert an issue with custom fields of various types (dropdown, radio, textarea .. so on)

UPDATE Issues
SET customfield_10050='[{"value":"AAA"},{"value":"BBB"}]'	--CUSTOM Checkboxes field update (Must use Raw JSON)
,customfield_10051='2020-12-31'	--CUSTOM Date field update
,customfield_10052='2020-12-31T23:59:59'	--CUSTOM DateTime field update

--Custom Dropdown / Radio fields
,customfield_10048_value='BBB'	--CUSTOM Dropdown field update (Using value - i.e. item label)
--OR--
--,customfield_10048_id='10022'	--CUSTOM Dropdown field update (Using id - i.e. item id)
--OR--
--,customfield_10048='{"value":"BBB"}'	--CUSTOM Dropdown (Using Raw value)

,customfield_10053='["bugfix","test"]'	--CUSTOM Labels field update (Must use Raw JSON)
,customfield_10057='[{"value":"AAA"},{"value":"BBB"}]'	--CUSTOM Listbox Multiselect field update (Must use Raw JSON)
,customfield_10049=123455555.123	--CUSTOM Number field update
,customfield_10054_text='Long string...line-1 
	Long string... line-2 
	Long string... line-3'	--CUSTOM Paragraph field update

,customfield_10055_value='BBB'	--CUSTOM Radio field update by value
--OR--
--,customfield_10055_id='10023'	--CUSTOM Radio field update

,customfield_10058='https://zappysys.com'	--CUSTOM Url field update
,customfield_10059_accountId="5dd64082af96bc0efbe55103" --CUSTOM User field update (update by accountId)
--OR--
--,customfield_10059='{"accountId":"5dd64082af96bc0efbe55103","displayName":"System"}'	--CUSTOM User field update --accountId=5dd64082af96bc0efbe55103
,customfield_10060='Custom text single line'	--CUSTOM text single line field 
,customfield_10001='577069e1-1bcd-4b1e-9070-0b2475830d1c'	--CUSTOM Team field update (update by Team Id)
--WHERE Id=10000
WITH (
	IssueIdOrKey='ISSKEY', --or use Id in where clause 
	Output=1,
	NotifyUsers=0,
	OverrideScreenSecurity=0,
	OverrideEditableFlag=0,
	ContinueOn404Error=0
)

--For INSERT use same values with same fields like below. For Insert you do not have to specify Id or Key
--INSERT INTO Issues(ProjectKey, IssueTypeName, Summary, Description, customfield_10050,....., customfield_10060) 
--VALUES ('SMP', 'Task', 'My ticket inserted through the API', 'A description about an issue', '[{"value":"AAA"},{"value":"BBB"}]' ,... 'Custom text single line')

'

UPDATE multiple Issues matching with JQL search query

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

Deletes a single issue

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

DELETE multiple Issues matching with JQL search query

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)

Update Custom Option Field (Dropdown/Radio)

This example shows how to update a custom field of an issue by Option Value or Id (Of option entry item)

--(By value)        
UPDATE Issues 
SET customfield_10048_value ='BBB' --supply value (label) of dropdown/radio
WITH (IssueIdOrKey='10020')

--OR-- (By item ID)

UPDATE Issues 
SET customfield_10048_id =10023 --supply id of dropdown/radio item
WITH (IssueIdOrKey='10020')

--OR-- (Raw id)

UPDATE Issues 
SET customfield_10048='{"id":"10023"}' --supply raw json
WITH (IssueIdOrKey='10020')

--OR--  (Raw value)
UPDATE Issues 
SET customfield_10048='{"value":"BBB"}' --supply raw json
WITH (IssueIdOrKey='10020')

--OR--  (set null)

UPDATE Issues 
SET customfield_10048 =null 
WITH (IssueIdOrKey='10020')

Update Custom Multi Select / User / Team fields (RAW Json Update)

This example shows how to update a custom field of type array or other complex fiels like user

UPDATE Issues 
SET customfield_10048='[{"value":"AAA"}, {"value":"CCC"}]' --supply raw json
WITH (IssueIdOrKey='10020')

--OR-- 

UPDATE Issues 
SET customfield_10048 =null --set to null
WITH (IssueIdOrKey='10020')

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data 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];

INSERT Issue

Inserts a single issue to a particular project

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

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

INSERT Multiple Issues (BULK Load from External System like Microsoft SQL Server / ODBC)

Inserts multiple issues from an external source (e.g. Microsoft SQL Server or ODBC)

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Issues(ProjectKey, IssueTypeName, Summary, Description)
SOURCE(''MSSQL'' --OR ODBC
, ''Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true''
--For ODBC
--, ''Dsn=MyOdbcDsn'' 
--, ''Driver={My ODBC Driver Type}...'' 
--Alias must match Supported Input Columns - Use Query Builder to find Input Column Names
,''select top 10 C_ProjKey as ProjectKey, C_Type as IssueTypeName, C_Summary as Summary, C_Desc as Description FROM Orders''

--Static value example
--,''select top 10 ''''CS'''' as ProjectKey, ''''New Feature'''' as IssueTypeName, ''''Task created - <<FUN_SEQUENCE>>'''' Summary, ''''A description <<FUN_NOW>>'''' Description FROM Orders''
)';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

UPDATE Issue

Updates an issue

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

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

UPDATE / Insert Issue with Custom Fields

Updates or Insert an issue with custom fields of various types (dropdown, radio, textarea .. so on)

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Issues
SET customfield_10050=''[{"value":"AAA"},{"value":"BBB"}]''	--CUSTOM Checkboxes field update (Must use Raw JSON)
,customfield_10051=''2020-12-31''	--CUSTOM Date field update
,customfield_10052=''2020-12-31T23:59:59''	--CUSTOM DateTime field update

--Custom Dropdown / Radio fields
,customfield_10048_value=''BBB''	--CUSTOM Dropdown field update (Using value - i.e. item label)
--OR--
--,customfield_10048_id=''10022''	--CUSTOM Dropdown field update (Using id - i.e. item id)
--OR--
--,customfield_10048=''{"value":"BBB"}''	--CUSTOM Dropdown (Using Raw value)

,customfield_10053=''["bugfix","test"]''	--CUSTOM Labels field update (Must use Raw JSON)
,customfield_10057=''[{"value":"AAA"},{"value":"BBB"}]''	--CUSTOM Listbox Multiselect field update (Must use Raw JSON)
,customfield_10049=123455555.123	--CUSTOM Number field update
,customfield_10054_text=''Long string...line-1 
	Long string... line-2 
	Long string... line-3''	--CUSTOM Paragraph field update

,customfield_10055_value=''BBB''	--CUSTOM Radio field update by value
--OR--
--,customfield_10055_id=''10023''	--CUSTOM Radio field update

,customfield_10058=''https://zappysys.com''	--CUSTOM Url field update
,customfield_10059_accountId="5dd64082af96bc0efbe55103" --CUSTOM User field update (update by accountId)
--OR--
--,customfield_10059=''{"accountId":"5dd64082af96bc0efbe55103","displayName":"System"}''	--CUSTOM User field update --accountId=5dd64082af96bc0efbe55103
,customfield_10060=''Custom text single line''	--CUSTOM text single line field 
,customfield_10001=''577069e1-1bcd-4b1e-9070-0b2475830d1c''	--CUSTOM Team field update (update by Team Id)
--WHERE Id=10000
WITH (
	IssueIdOrKey=''ISSKEY'', --or use Id in where clause 
	Output=1,
	NotifyUsers=0,
	OverrideScreenSecurity=0,
	OverrideEditableFlag=0,
	ContinueOn404Error=0
)

--For INSERT use same values with same fields like below. For Insert you do not have to specify Id or Key
--INSERT INTO Issues(ProjectKey, IssueTypeName, Summary, Description, customfield_10050,....., customfield_10060) 
--VALUES (''SMP'', ''Task'', ''My ticket inserted through the API'', ''A description about an issue'', ''[{"value":"AAA"},{"value":"BBB"}]'' ,... ''Custom text single line'')

''';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

UPDATE multiple Issues matching with JQL search query

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

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DELETE Issue

Deletes a single issue

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Issues
WITH (IssueIdOrKey=''10020'', OUTPUT=1, ContinueOn404Error=0)';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DELETE multiple Issues matching with JQL search query

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

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

Update Custom Option Field (Dropdown/Radio)

This example shows how to update a custom field of an issue by Option Value or Id (Of option entry item)

DECLARE @MyQuery NVARCHAR(MAX) = '--(By value)        
UPDATE Issues 
SET customfield_10048_value =''BBB'' --supply value (label) of dropdown/radio
WITH (IssueIdOrKey=''10020'')

--OR-- (By item ID)

UPDATE Issues 
SET customfield_10048_id =10023 --supply id of dropdown/radio item
WITH (IssueIdOrKey=''10020'')

--OR-- (Raw id)

UPDATE Issues 
SET customfield_10048=''{"id":"10023"}'' --supply raw json
WITH (IssueIdOrKey=''10020'')

--OR--  (Raw value)
UPDATE Issues 
SET customfield_10048=''{"value":"BBB"}'' --supply raw json
WITH (IssueIdOrKey=''10020'')

--OR--  (set null)

UPDATE Issues 
SET customfield_10048 =null 
WITH (IssueIdOrKey=''10020'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

Update Custom Multi Select / User / Team fields (RAW Json Update)

This example shows how to update a custom field of type array or other complex fiels like user

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Issues 
SET customfield_10048=''[{"value":"AAA"}, {"value":"CCC"}]'' --supply raw json
WITH (IssueIdOrKey=''10020'')

--OR-- 

UPDATE Issues 
SET customfield_10048 =null --set to null
WITH (IssueIdOrKey=''10020'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];