Reference

Endpoint Get Work Items by Ids

Name

get_workitems_by_ids

Description

Get work items in the default project within the organization by their work item Ids. [API reference]

Related Tables

WorkItems

Parameters

Parameter Required Options
Name: Project

Label: Project Name

The Name of the project. 		YES
Name: Ids

Label: Work Item Id List (comma separated)

The comma-separated list of requested work item ids. (Maximum 200 ids allowed).
Name: AsOf

Label: As-Of Date

AsOf UTC date time string.
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
Name: ErrorPolicy

Label: Error Policy

The flag to control error policy in a bulk get work items request. Possible options are 'Fail' and 'Omit'.
Option Value
Fail Fail
Omit Omit
Name: Fields

Label: Fields

Comma-separated list of requested fields.

Output Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
Id DT_I4 int
Title DT_WSTR nvarchar(1000) 1000
Description DT_WSTR nvarchar(4000) 4000
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
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_WSTR nvarchar(2000) 2000
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_WSTR nvarchar(4000) 4000
Steps DT_WSTR nvarchar(4000) 4000
ReproSteps DT_WSTR nvarchar(4000) 4000
[$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

This Endpoint belongs to the WorkItems table, therefore it is better to use it, instead of accessing the endpoint directly:

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
WorkItems
Required Parameters
Project Name Fill-in the parameter...
Optional Parameters
Work Item Id List (comma separated)
As-Of Date
Expand Parameters
Error Policy
Fields
Continue processing on 404 error True
SSIS API Source - Read from table or endpoint

API Destination

This Endpoint belongs to the WorkItems table, therefore it is better to use it, instead of accessing the endpoint directly. Use this table and table-operation pair to get work items by ids:

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
WorkItems
Lookup
Required Parameters
Project Name Fill-in the parameter...
Optional Parameters
Work Item Id List (comma separated)
As-Of Date
Expand Parameters
Error Policy
Fields
Continue processing on 404 error True
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Get multiple work items by their Ids (Comma-separated) 

SELECT * FROM get_workitems_by_ids WITH (ids='6444,5578,9467')

Get a specific work item by its Id 

SELECT * FROM WorkItems WHERE Id=1001

Get a specific work item by its Id, but halt the process if a 404 error is encountered 

SELECT * FROM WorkItems WHERE Id='8563' WITH (ContineOn404Error='False')

get_workitems_by_ids endpoint belongs to WorkItems 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:

Get multiple work items by their Ids (Comma-separated) 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_workitems_by_ids WITH (ids=''6444,5578,9467'')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Get a specific work item by its Id 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WHERE Id=1001';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Get a specific work item by its Id, but halt the process if a 404 error is encountered 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WHERE Id=''8563'' WITH (ContineOn404Error=''False'')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

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