Reference

Endpoint Get Work Items for Specified Query Id

Name

get_workitems_by_query_id

Description

Get work items associated with the specified project, organization and query. (A team can optionally be specified as well.). Read more about this API here https://learn.microsoft.com/en-us/rest/api/azure/devops/wit/wiql/query-by-id?view=azure-devops-rest-7.1 [API reference]

Parameters

Parameter Required Options
Name: Project

Label: Project Name

The Name of the project. 		YES
Name: Query

Label: Query Id

The Id of the predefined Query. 		YES
Name: Team

Label: Team Id or Name

Id or name of the team associated with the query.
Name: TimePrecision

Label: Use time precision

Whether or not to use time precision.
Option Value
false false
true true
Name: Expand

Label: Expand Parameters

The expand parameters for work item attributes. Possible options are 'None', 'Relations', 'Fields', 'Links', and 'All'.
Option Value
None None
Relations Relations
Fields Fields
Links Links
All All

Output Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
Id DT_I4 int
Title DT_WSTR nvarchar(1000) 1000
Description DT_NTEXT nvarchar(MAX)
WorkItemType DT_WSTR nvarchar(100) 100
State DT_WSTR nvarchar(100) 100
Url DT_WSTR nvarchar(2048) 2048
Tags DT_WSTR nvarchar(4000) 4000
Revision DT_I4 int
AreaPath DT_WSTR nvarchar(500) 500
TeamProject DT_WSTR nvarchar(500) 500
IterationPath DT_WSTR nvarchar(500) 500
Reason DT_WSTR nvarchar(500) 500
CreatedDate DT_DBTIMESTAMP datetime
ChangedDate DT_DBTIMESTAMP datetime
CommentCount DT_I4 int
ParentId DT_I4 int Only returned if Expand Parameter is set to Relations or All
AssignedToDisplayName DT_WSTR nvarchar(500) 500
AssignedToUrl DT_WSTR nvarchar(2048) 2048
AssignedToLinksAvatarHref DT_WSTR nvarchar(4000) 4000
AssignedToId DT_WSTR nvarchar(500) 500
AssignedToUniqueName DT_WSTR nvarchar(500) 500
AssignedToImageUrl DT_WSTR nvarchar(2048) 2048
AssignedToDescriptor DT_WSTR nvarchar(2000) 2000
CreatedByDisplayName DT_WSTR nvarchar(500) 500
CreatedByUrl DT_WSTR nvarchar(2048) 2048
CreatedByLinksAvatarHref DT_WSTR nvarchar(2048) 2048
CreatedById DT_WSTR nvarchar(500) 500
CreatedByUniqueName DT_WSTR nvarchar(500) 500
CreatedByImageUrl DT_WSTR nvarchar(2048) 2048
CreatedByDescriptor DT_WSTR nvarchar(2000) 2000
ChangedByDisplayName DT_WSTR nvarchar(500) 500
ChangedByUrl DT_WSTR nvarchar(2048) 2048
ChangedByLinksAvatarHref DT_WSTR nvarchar(2048) 2048
ChangedById DT_WSTR nvarchar(500) 500
ChangedByUniqueName DT_WSTR nvarchar(500) 500
ChangedByImageUrl DT_WSTR nvarchar(2048) 2048
ChangedByDescriptor DT_WSTR nvarchar(2000) 2000
Watermark DT_WSTR nvarchar(500) 500
StateChangeDate DT_DBTIMESTAMP datetime
Priority DT_I4 int
Triage DT_WSTR nvarchar(1000) 1000
StackRank DT_I4 int
Blocked DT_WSTR nvarchar(500) 500
TaskType DT_WSTR nvarchar(500) 500
RequiresReview DT_WSTR nvarchar(100) 100
RequiresTest DT_WSTR nvarchar(100) 100
ActivatedDate DT_DBTIMESTAMP datetime
ActivatedByDisplayName DT_WSTR nvarchar(500) 500
ActivatedByUrl DT_WSTR nvarchar(2048) 2048
ActivatedByLinksAvatarHref DT_WSTR nvarchar(2048) 2048
ActivatedById DT_WSTR nvarchar(500) 500
ActivatedByUniqueName DT_WSTR nvarchar(500) 500
ActivatedByImageUrl DT_WSTR nvarchar(2048) 2048
ActivatedByDescriptor DT_WSTR nvarchar(2000) 2000
ClosedDate DT_DBTIMESTAMP datetime
ClosedByDisplayName DT_WSTR nvarchar(500) 500
ClosedByUrl DT_WSTR nvarchar(2048) 2048
ClosedByLinksAvatarHref DT_WSTR nvarchar(2048) 2048
ClosedById DT_WSTR nvarchar(500) 500
ClosedByUniqueName DT_WSTR nvarchar(500) 500
ClosedByImageUrl DT_WSTR nvarchar(2048) 2048
ClosedByDescriptor DT_WSTR nvarchar(2000) 2000
ResolvedDate DT_DBTIMESTAMP datetime
ResolvedByDisplayName DT_WSTR nvarchar(500) 500
ResolvedByUrl DT_WSTR nvarchar(2048) 2048
ResolvedByLinksAvatarHref DT_WSTR nvarchar(2048) 2048
ResolvedById DT_WSTR nvarchar(500) 500
ResolvedByUniqueName DT_WSTR nvarchar(500) 500
ResolvedByImageUrl DT_WSTR nvarchar(2048) 2048
ResolvedByDescriptor DT_WSTR nvarchar(2000) 2000
ResolvedReason DT_WSTR nvarchar(4000) 4000
AreaId DT_WSTR nvarchar(500) 500
IterationId DT_WSTR nvarchar(500) 500
RevisedDate DT_DBTIMESTAMP datetime
RemoteLinkCount DT_I4 int
RelatedLinkCount DT_I4 int
HyperLinkCount DT_I4 int
ExternalLinkCount DT_I4 int
History DT_NTEXT nvarchar(MAX)
IntegrationBuild DT_WSTR nvarchar(500) 500
FoundIn DT_WSTR nvarchar(500) 500
OriginalEstimate DT_R4 real
RemainingWork DT_R4 real
CompletedWork DT_R4 real
Size DT_R4 real
Effort DT_R4 real
TargetDate DT_DBTIMESTAMP datetime
StoryPoints DT_R4 real
StartDate DT_DBTIMESTAMP datetime
FinishDate DT_DBTIMESTAMP datetime
DueDate DT_DBTIMESTAMP datetime
Discipline DT_WSTR nvarchar(500) 500
SystemInfo DT_NTEXT nvarchar(MAX)
Steps DT_NTEXT nvarchar(MAX)
ReproSteps DT_NTEXT nvarchar(MAX)
Severity DT_WSTR nvarchar(100) 100
ProposedFix DT_NTEXT nvarchar(MAX)
Symptom DT_NTEXT nvarchar(MAX)
RootCause DT_WSTR nvarchar(100) 100
HowFound DT_WSTR nvarchar(100) 100
FoundInEnvironment DT_WSTR nvarchar(100) 100
BusinessValue DT_I4 int
ValueArea DT_WSTR nvarchar(100) 100
TestedBy DT_WSTR nvarchar(500) 500
TestPriority DT_I4 int
Activity DT_WSTR nvarchar(100) 100
[$parent.referenceName$] DT_WSTR nvarchar(4000) 4000
If the column you are looking for is missing, consider customizing Azure DevOps Connector.

Input Columns

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

Examples

SSIS

Use Azure DevOps Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Source

API Source - Azure DevOps
Azure DevOps Connector can be used to integrate Azure DevOps data in your App / BI Tools. You can create, read, query, modify, and delete WorkItems, Projects, Teams and more.
Azure DevOps
Get Work Items for Specified Query Id
Required Parameters
Project Name Fill-in the parameter...
Query Id Fill-in the parameter...
Optional Parameters
Team Id or Name
Use time precision
Expand Parameters Relations
SSIS API Source - Read from table or endpoint

API Destination

API Destination - Azure DevOps
Azure DevOps Connector can be used to integrate Azure DevOps data in your App / BI Tools. You can create, read, query, modify, and delete WorkItems, Projects, Teams and more.
Azure DevOps
Get Work Items for Specified Query Id
Required Parameters
Project Name Fill-in the parameter...
Query Id Fill-in the parameter...
Optional Parameters
Team Id or Name
Use time precision
Expand Parameters Relations
SSIS API Destination - Access table or endpoint

ODBC application

Use these SQL queries in your ODBC application data source:

Get workitems for a predefiended query id

If you created a shared query for team or your private query then you can get result of that by query id. 

SELECT * FROM get_workitems_by_query_id
WITH(
	  Query='e5150e18-1323-485d-8937-6991bf09ab1c' --see get_queries endpoint
	, Project='Odbc'
)

SQL Server

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

Get workitems for a predefiended query id

If you created a shared query for team or your private query then you can get result of that by query id. 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_workitems_by_query_id
WITH(
	  Query=''e5150e18-1323-485d-8937-6991bf09ab1c'' --see get_queries endpoint
	, Project=''Odbc''
)';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];