Reference

Table Issues

Description

No description available

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_issue

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

API Source - Jira

Read and write Jira data effortlessly. Track, manage, and automate issues, projects, worklogs, and comments — almost no coding required.

Jira

Issues

There are no parameters to configure.

SSIS API Source - Read from table or endpoint

Read/write to Issues table using API Destination

API Destination - Jira

Read and write Jira data effortlessly. Track, manage, and automate issues, projects, worklogs, and comments — almost no coding required.

Jira

Issues

Select

There are no parameters to configure.

SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Read issues

Gets all issues. By default the query returns every issue; you can narrow results by adding a <code>WITH</code> clause to search by Key, Project, or JQL expression (see the commented options in the code).Use <code>SearchBy='Key'</code> with a single key or ID for one issue, or comma-separated keys/IDs for multiple. Use <code>SearchBy='Project'</code> to limit to one or more project codes. Use <code>SearchBy='Jql'</code> with a JQL expression for advanced filtering.

SELECT * FROM Issues

--//Query single issue by numeric Issue Id
--SELECT * FROM Issues Where Id=101234

--//Query issue by numeric Issue Ids (multiple)
--SELECT * FROM Issues WITH(SearchBy='Key', Key='101234,101235,101236')

--//Query issue by Issue Key(s) (alpha-numeric)
--SELECT * FROM Issues WITH(SearchBy='Key', Key='PROJ-11')
--SELECT * FROM Issues WITH(SearchBy='Key', Key='PROJ-11,PROJ-12,PROJ-13')

--//Query issue by project(s)
--SELECT * FROM Issues WITH(SearchBy='Project', Project='PROJ')
--SELECT * FROM Issues WITH(SearchBy='Project', Project='PROJ,KAN,CS')

--//Query issue by JQL expression
--SELECT * FROM Issues WITH(SearchBy='Jql', Jql='status IN (Done, Closed) AND created > -5d' )

Read an issue by ID

Gets a single issue by its key (e.g. CS-123) or numeric ID (e.g. 10001). Supply the key or ID in the <code>Key</code> parameter; the connector returns that issue’s fields.

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

Read an issue by ID — continue on specific error

By default, if the issue is not found or the search condition is invalid, the query returns an error. You can tell the connector to continue instead by setting <code>ContineOnErrorForMessage=1</code> and supplying the error message (or a substring) to ignore in <code>ErrorSubstringToMatch</code>.

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

Read an issue by Key

Gets a single issue by its project key and number (e.g. CS-1). Same as reading by ID; use whichever identifier you have.

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

Read multiple issues by ID or Key

Gets multiple issues in one request. Supply comma-separated keys and/or numeric IDs in the <code>Key</code> parameter; the connector returns all matching issues.

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

Read issues for a project

Gets all issues belonging to a given project (or projects). Supply one or more project codes in <code>Project</code>; use a comma-separated list for multiple projects.

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

Read issues (specific fields only)

Gets all issues but returns only the columns you specify in <code>Fields</code>, which can speed up the query when you need just a few fields. Use a comma-separated list of field names; <code>*all</code> returns every field.

SELECT * FROM Issues WITH(Fields='id,key,summary,status')

Read issues using JQL query

Gets issues that match a JQL (Jira Query Language) expression. Supply the JQL string in the <code>Jql</code> parameter; you can filter by status, project, date, assignee, text, and more.See the commented examples in the code for common patterns (IDs, keys, dates, text search, etc.). JQL reference links are included in the snippet.

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"

*/

Create an issue

Creates a single issue in a project. Supply at least <code>ProjectKey</code>, <code>IssueTypeName</code>, <code>Summary</code>, and optionally <code>Description</code> and other columns. Use <code>WITH (Output=1)</code> to return the created issue.

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

Create multiple issues (bulk from external source)

Creates multiple issues by loading rows from an external source such as Microsoft SQL Server or another ODBC database. Use <code>SOURCE('MSSQL', connectionString, query)</code> (or <code>ODBC</code> / driver connection) so that column aliases in the query match the issue input columns (e.g. <code>ProjectKey</code>, <code>IssueTypeName</code>, <code>Summary</code>, <code>Description</code>).

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 an issue

Updates an existing issue. Set the columns you want to change (<code>Summary</code>, <code>Description</code>, <code>Labels</code>, <code>DueDate</code>, etc.) and identify the issue with <code>IssueIdOrKey</code> in <code>WITH</code> or in a <code>WHERE</code> clause. Optional <code>WITH</code> parameters control notifications and security overrides.

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 or create an issue with custom fields

Updates or creates an issue including custom fields of various types: dropdown, radio, checkboxes, date, labels, number, paragraph, URL, user, team, and single-line text. Use the custom field ID (e.g. <code>customfield_10050</code>) and the appropriate suffix or raw JSON format as shown in the example. For insert, use the same column list in <code>INSERT INTO Issues(...)</code>.

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 by JQL

Updates every issue that matches a JQL expression. Add a <code>WHERE</code> condition (e.g. <code>Where 1=1</code>) so the connector uses the lookup path, then supply the JQL in <code>WITH (jql='...')</code>. All matching issues receive the same column updates.

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 an issue

Deletes a single issue. Identify the issue with <code>IssueIdOrKey</code> in the <code>WITH</code> clause. Set <code>ContinueOn404Error=0</code> to treat a missing issue as an error, or <code>1</code> to ignore it.

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

Delete multiple issues by JQL

Deletes every issue that matches a JQL expression. Add a <code>WHERE</code> condition (e.g. <code>Where 1=1</code>) so the connector uses the lookup path, then supply the JQL in <code>WITH (jql='...')</code>. Use with care; all matching issues are removed.

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 or radio)

Shows how to update a custom field that uses a single option (dropdown or radio). You can set the value by option label (<code>customfield_xxxx_value</code>), by option ID (<code>customfield_xxxx_id</code>), or with raw JSON. To clear the field, set it to <code>null</code>. Replace the custom field ID with your own from <code>Fields</code>.

--(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, or team fields (raw JSON)

Shows how to update custom fields that hold multiple values or complex types (e.g. multi-select, user, team) using raw JSON. Supply a JSON array or object as required by the field type; to clear, set the column to <code>null</code>. Use <code>SELECT * FROM Fields</code> to find your custom field IDs.

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')

Read issues with all columns (including SLA / nested custom fields)

Gets issues with all columns exposed, including SLA and nested custom fields, without defining a <code>META</code> parameter. Set the connector’s Metadata Mode to <code>MergeStaticDynamic</code> so the driver scans sample data and merges static and dynamic columns. This approach is simpler but slower because of the extra requests used to infer column types from data.

-- No META needed. Set MetaDetectionOrder to MergeStaticDynamic so nested/SLA columns are discovered automatically.
-- Slower: driver makes extra requests to scan data and merge with static metadata.

SELECT *
FROM Issues
WITH(
	  JQL='project IN(SUP)'
	  ,MetaDetectionOrder='MergeStaticDynamic'
)

Read custom fields with nested structures (SLA fields)

Shows how to query custom fields that contain nested document structures (not arrays), such as SLA fields. Use <code>META='@OverrideMode:1'</code> to merge static and dynamic metadata so you can select both standard columns and nested custom field properties. Replace the example custom field IDs (e.g. <code>customfield_10084</code>) with your own; run <code>SELECT * FROM Fields</code> or check Jira project settings to find field IDs.

-- NOTE: Replace customfield_10084, customfield_10085, customfield_10086 with your own custom field IDs.
-- You can run [SELECT * FROM Fields] in preview tab to get the field IDs. 
-- OR you can find field IDs in Jira: Project Settings > Issue types > Edit field > field ID in URL or API.

SELECT 
-- static fields

  id
, key
, summary
, statusname

-- dynamic fields

-- SLA: Time to Resolution (replace customfield_10084 with your SLA field ID) 

,[fields.customfield_10084.id] as c10084_id
,[fields.customfield_10084.name] as c10084_name
,[fields.customfield_10084.ongoingCycle.startTime.friendly] as c10084_startTime_friendly
,[fields.customfield_10084.ongoingCycle.startTime.jira] as c10084_startTime_jira
,[fields.customfield_10084.ongoingCycle.breachTime.friendly] as c10084_breachTime_friendly
,[fields.customfield_10084.ongoingCycle.breachTime.jira] as c10084_breachTime_jira
,[fields.customfield_10084.ongoingCycle.elapsedTime.friendly] as c10084_elapsedTime_friendly
,[fields.customfield_10084.ongoingCycle.remainingTime.friendly] as c10084_remainingTime_friendly
,[fields.customfield_10084.ongoingCycle.goalDuration.friendly] as c10084_goalDuration_friendly
,[fields.customfield_10084.ongoingCycle.breached] as c10084_breached
,[fields.customfield_10084.ongoingCycle.paused] as c10084_paused

-- SLA: Time to First Response (replace customfield_10085 with your SLA field ID)

,[fields.customfield_10085.id] as c10085_id
,[fields.customfield_10085.name] as c10085_name
,[fields.customfield_10085.ongoingCycle.startTime.friendly] as c10085_startTime_friendly
,[fields.customfield_10085.ongoingCycle.startTime.jira] as c10085_startTime_jira
,[fields.customfield_10085.ongoingCycle.breachTime.friendly] as c10085_breachTime_friendly
,[fields.customfield_10085.ongoingCycle.breachTime.jira] as c10085_breachTime_jira
,[fields.customfield_10085.ongoingCycle.elapsedTime.friendly] as c10085_elapsedTime_friendly
,[fields.customfield_10085.ongoingCycle.remainingTime.friendly] as c10085_remainingTime_friendly
,[fields.customfield_10085.ongoingCycle.goalDuration.friendly] as c10085_goalDuration_friendly
,[fields.customfield_10085.ongoingCycle.breached] as c10085_breached
,[fields.customfield_10085.ongoingCycle.paused] as c10085_paused

-- SLA: Time to Close After Resolution (replace customfield_10086 with your SLA field ID)

,[fields.customfield_10086.id] as c10086_id
,[fields.customfield_10086.name] as c10086_name

-- Other nested custom fields (replace IDs as needed) 
,[fields.customfield_10024.id] as c10024_id
,[fields.customfield_10024.name] as c10024_name
,[fields.customfield_10075.languageCode] as c10075_languageCode
,[fields.customfield_10075.displayName] as c10075_displayName

FROM Issues
WITH(
	  JQL='project IN(SUP)'
	  -- @OverrideMode:1 merges metadata so you can use static + dynamic fields together
	  ,META='@OverrideMode:1
	  ;fields.customfield_10084.id : string(10)
	  ;fields.customfield_10084.name : string(180)
	  ;fields.customfield_10084.ongoingCycle.startTime.friendly : string(130)
	  ;fields.customfield_10084.ongoingCycle.startTime.jira : DateTime
	  ;fields.customfield_10084.ongoingCycle.breachTime.friendly : string(130)
	  ;fields.customfield_10084.ongoingCycle.breachTime.jira : DateTime
	  ;fields.customfield_10084.ongoingCycle.elapsedTime.friendly : string(30)
	  ;fields.customfield_10084.ongoingCycle.remainingTime.friendly : string(70)
	  ;fields.customfield_10084.ongoingCycle.goalDuration.friendly : string(30)
	  ;fields.customfield_10084.ongoingCycle.breached : Boolean
	  ;fields.customfield_10084.ongoingCycle.paused : Boolean
	  ;fields.customfield_10085.id : string(10)
	  ;fields.customfield_10085.name : string(220)
	  ;fields.customfield_10085.ongoingCycle.startTime.friendly : string(130)
	  ;fields.customfield_10085.ongoingCycle.startTime.jira : DateTime
	  ;fields.customfield_10085.ongoingCycle.breachTime.friendly : string(130)
	  ;fields.customfield_10085.ongoingCycle.breachTime.jira : DateTime
	  ;fields.customfield_10085.ongoingCycle.elapsedTime.friendly : string(30)
	  ;fields.customfield_10085.ongoingCycle.remainingTime.friendly : string(70)
	  ;fields.customfield_10085.ongoingCycle.goalDuration.friendly : string(30)
	  ;fields.customfield_10085.ongoingCycle.breached : Boolean
	  ;fields.customfield_10085.ongoingCycle.paused : Boolean
	  ;fields.customfield_10086.id : string(10)
	  ;fields.customfield_10086.name : string(300)
	  ;fields.customfield_10024.id : string(40)
	  ;fields.customfield_10024.name : string(80)
	  ;fields.customfield_10075.languageCode : string(20)
	  ;fields.customfield_10075.displayName : string(70)
	  ;--add more nested custom field properties here as needed--
	  '
)

SQL Server

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