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
Parameters
Output Columns
Label | Data Type (SSIS) | Data Type (SQL) | Length | Raw | Description |
---|---|---|---|---|---|
Id |
DT_I4
|
int
|
False |
||
Title |
DT_WSTR
|
nvarchar(1000)
|
1000 | False |
|
Description |
DT_WSTR
|
nvarchar(4000)
|
4000 | False |
|
WorkItemType |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
State |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
Url |
DT_WSTR
|
nvarchar(2048)
|
2048 | False |
|
Tags |
DT_WSTR
|
nvarchar(4000)
|
4000 | False |
|
Revision |
DT_I4
|
int
|
False |
||
AreaPath |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
TeamProject |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
IterationPath |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
Reason |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
CreatedDate |
DT_DBTIMESTAMP
|
datetime
|
False |
||
ChangedDate |
DT_DBTIMESTAMP
|
datetime
|
False |
||
CommentCount |
DT_I4
|
int
|
False |
||
AssignedToDisplayName |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
AssignedToUrl |
DT_WSTR
|
nvarchar(2048)
|
2048 | False |
|
AssignedToLinksAvatarHref |
DT_WSTR
|
nvarchar(4000)
|
4000 | False |
|
AssignedToId |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
AssignedToUniqueName |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
AssignedToImageUrl |
DT_WSTR
|
nvarchar(2048)
|
2048 | False |
|
AssignedToDescriptor |
DT_WSTR
|
nvarchar(2000)
|
2000 | False |
|
CreatedByDisplayName |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
CreatedByUrl |
DT_WSTR
|
nvarchar(2048)
|
2048 | False |
|
CreatedByLinksAvatarHref |
DT_WSTR
|
nvarchar(2048)
|
2048 | False |
|
CreatedById |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
CreatedByUniqueName |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
CreatedByImageUrl |
DT_WSTR
|
nvarchar(2048)
|
2048 | False |
|
CreatedByDescriptor |
DT_WSTR
|
nvarchar(2000)
|
2000 | False |
|
ChangedByDisplayName |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
ChangedByUrl |
DT_WSTR
|
nvarchar(2048)
|
2048 | False |
|
ChangedByLinksAvatarHref |
DT_WSTR
|
nvarchar(2048)
|
2048 | False |
|
ChangedById |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
ChangedByUniqueName |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
ChangedByImageUrl |
DT_WSTR
|
nvarchar(2048)
|
2048 | False |
|
ChangedByDescriptor |
DT_WSTR
|
nvarchar(2000)
|
2000 | False |
|
Watermark |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
StateChangeDate |
DT_DBTIMESTAMP
|
datetime
|
False |
||
Priority |
DT_I4
|
int
|
False |
||
Triage |
DT_WSTR
|
nvarchar(1000)
|
1000 | False |
|
StackRank |
DT_I4
|
int
|
False |
||
Blocked |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
TaskType |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
RequiresReview |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
RequiresTest |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
ActivatedDate |
DT_DBTIMESTAMP
|
datetime
|
False |
||
ActivatedByDisplayName |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
ActivatedByUrl |
DT_WSTR
|
nvarchar(2048)
|
2048 | False |
|
ActivatedByLinksAvatarHref |
DT_WSTR
|
nvarchar(2048)
|
2048 | False |
|
ActivatedById |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
ActivatedByUniqueName |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
ActivatedByImageUrl |
DT_WSTR
|
nvarchar(2048)
|
2048 | False |
|
ActivatedByDescriptor |
DT_WSTR
|
nvarchar(2000)
|
2000 | False |
|
ClosedDate |
DT_DBTIMESTAMP
|
datetime
|
False |
||
ClosedByDisplayName |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
ClosedByUrl |
DT_WSTR
|
nvarchar(2048)
|
2048 | False |
|
ClosedByLinksAvatarHref |
DT_WSTR
|
nvarchar(2048)
|
2048 | False |
|
ClosedById |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
ClosedByUniqueName |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
ClosedByImageUrl |
DT_WSTR
|
nvarchar(2048)
|
2048 | False |
|
ClosedByDescriptor |
DT_WSTR
|
nvarchar(2000)
|
2000 | False |
|
ResolvedDate |
DT_DBTIMESTAMP
|
datetime
|
False |
||
ResolvedByDisplayName |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
ResolvedByUrl |
DT_WSTR
|
nvarchar(2048)
|
2048 | False |
|
ResolvedByLinksAvatarHref |
DT_WSTR
|
nvarchar(2048)
|
2048 | False |
|
ResolvedById |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
ResolvedByUniqueName |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
ResolvedByImageUrl |
DT_WSTR
|
nvarchar(2048)
|
2048 | False |
|
ResolvedByDescriptor |
DT_WSTR
|
nvarchar(2000)
|
2000 | False |
|
ResolvedReason |
DT_WSTR
|
nvarchar(4000)
|
4000 | False |
|
AreaId |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
IterationId |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
RevisedDate |
DT_DBTIMESTAMP
|
datetime
|
False |
||
RemoteLinkCount |
DT_I4
|
int
|
False |
||
RelatedLinkCount |
DT_I4
|
int
|
False |
||
HyperLinkCount |
DT_I4
|
int
|
False |
||
ExternalLinkCount |
DT_I4
|
int
|
False |
||
History |
DT_WSTR
|
nvarchar(2000)
|
2000 | False |
|
IntegrationBuild |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
FoundIn |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
OriginalEstimate |
DT_R4
|
real
|
False |
||
RemainingWork |
DT_R4
|
real
|
False |
||
CompletedWork |
DT_R4
|
real
|
False |
||
Size |
DT_R4
|
real
|
False |
||
Effort |
DT_R4
|
real
|
False |
||
TargetDate |
DT_DBTIMESTAMP
|
datetime
|
False |
||
StoryPoints |
DT_R4
|
real
|
False |
||
StartDate |
DT_DBTIMESTAMP
|
datetime
|
False |
||
FinishDate |
DT_DBTIMESTAMP
|
datetime
|
False |
||
DueDate |
DT_DBTIMESTAMP
|
datetime
|
False |
||
Discipline |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
SystemInfo |
DT_WSTR
|
nvarchar(4000)
|
4000 | False |
|
Steps |
DT_WSTR
|
nvarchar(4000)
|
4000 | False |
|
ReproSteps |
DT_WSTR
|
nvarchar(4000)
|
4000 | False |
|
[$parent.referenceName$] |
DT_WSTR
|
nvarchar(4000)
|
4000 | False |
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 Azure DevOps Connector in API Source component to read data or in API Destination component to read/write data:
Lookup a row in WorkItems table using API Destination
This Endpoint belongs to WorkItems table, therefore you cannot work with it directly. Use this table and table-operation pair instead:

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')
Query work items using Wiql (with #DirectSQL Tag)
This example shows how to use WIQL language to query Azure DevOps WorkItems using #DirectSQL pre processor to invoke server side query engine. This one is same as previous example except simplified SQL. To learn more about WIQL Syntax you can refer this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops
#DirectSQL SELECT * FROM WorkItems WHERE [System.TeamProject]=''ProductTesting'' and [System.Id]=6455 ORDER BY [System.Id] DESC')
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];
Query work items using Wiql (with #DirectSQL Tag)
This example shows how to use WIQL language to query Azure DevOps WorkItems using #DirectSQL pre processor to invoke server side query engine. This one is same as previous example except simplified SQL. To learn more about WIQL Syntax you can refer this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops
DECLARE @MyQuery NVARCHAR(MAX) = '#DirectSQL SELECT * FROM WorkItems WHERE [System.TeamProject]=''''ProductTesting'''' and [System.Id]=6455 ORDER BY [System.Id] DESC'')';
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).