Reference

Endpoint Update Work Item

Name

update_workitem

Description

Updates an existing work item in the default project within the organization. [API reference]

Related Tables

WorkItems

Parameters

Parameter Required Options
Name: Project

Label: Project Name

The Name of the project. 		YES
Name: Id

Label: Work Item Id
Name: Operation

Label: Operation

Some operation requires you to supply 'add' rather than 'replace' (e.g. adding new tag)
Option Value
replace replace
add add
remove remove
copy copy
move move
test test

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
Id DT_WSTR nvarchar(4000) 4000
WorkItemType DT_WSTR nvarchar(1000) 1000
Title DT_WSTR nvarchar(1000) 1000
Description DT_WSTR nvarchar(4000) 4000
State DT_WSTR nvarchar(100) 100
History DT_WSTR nvarchar(100) 100
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
AssignedTo DT_WSTR nvarchar(500) 500
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
ActivatedBy DT_WSTR nvarchar(500) 500
ClosedDate DT_DBTIMESTAMP datetime
ClosedBy DT_WSTR nvarchar(500) 500
ResolvedDate DT_DBTIMESTAMP datetime
ResolvedReason DT_WSTR nvarchar(4000) 4000
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
StartDate DT_DBTIMESTAMP datetime
FinishDate DT_DBTIMESTAMP datetime
DueDate DT_DBTIMESTAMP datetime
StoryPoints DT_R4 real
Discipline DT_WSTR nvarchar(100) 100
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
Required columns that you need to supply are bolded.

Examples

SSIS

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

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 update work item:

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
Update
Required Parameters
Project Name Fill-in the parameter...
Optional Parameters
Work Item Id
Continue processing on 404 error True
Operation replace
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Update a specific work item by its Id 

UPDATE WorkItems SET
Title = 'Renamed Work Item Title',
CustomMyField = 'New Custom Text'
WHERE Id = 7455

Update a work item by its Id (many columns) 

UPDATE WorkItems
SET   [Title] = 'Update-QA Task <<FUN_NOW>>'
	, [Description] = 'Updated desc <<FUN_NOW>>'
  --, [WorkItemType]='Bug' -- Task
	, [State] = 'Active'
	, [Tags] = 'odata; api'
	, [Revision] = 1
	, [AreaPath] = 'ProductTesting\SSISPowerPack'
	, [TeamProject] = 'ProductTesting'
	, [IterationPath] = 'ProductTesting\2021.6'
	, [Reason] = 'Reactivated'
	, [AssignedTo] = 'build@mycompany.com'
	, [Priority] = 1
  --, [Triage] = 'Pending'	
  --, [StackRank] = ???
	, [Blocked] = 'No'
	, [TaskType] = 'Planned'
	, [RequiresReview] = 'No'
	, [RequiresTest] = 'No'
	, [ActivatedDate] = '2021-01-31'
  --, [ResolvedReason] = 'Complete and Requires Review/Test'
	, [IntegrationBuild] = '101.1.2.3'
	, [FoundIn] = '100.1.2.3'
	, [OriginalEstimate] = 9999.123
	, [RemainingWork] = 9999.123
	, [CompletedWork] = 9999.123
	, [Size] = 9999.123
	, [Effort] = 9999.123
	, [TargetDate] = '2021-01-31'
	, [StartDate] = '2021-01-31'
	, [FinishDate] = '2021-01-31'
	, [DueDate] = '2021-01-31'
	, [StoryPoints] = 100.5
--	, [Discipline] = 'abcd'
	, [SystemInfo] = 'abcd'
	, [Steps] = 'abcd'
	, [ReproSteps] = 'abcd'
--	, [CustomFieldText] = 'abcd' 
--	, [CustomFieldDate] = '2021-01-31' 
--	, [CustomFieldNumber] = 9999  
--	, [CustomFieldDecimal] = 9999.123 
--	, [CustomFieldPickListString] = 'BBB' 
--	, [CustomFieldPickListNum] = '2' 
WHERE [Id] = 6455

Add Tags to an exising workitem 

UPDATE WorkItems
SET   Tags = 'newtag1; newtag2'
WHERE Id = 6455
WITH(
	   Project='ProductTesting'
	  ,Operation='add'
)

Update work items match with Wiql Query 

UPDATE WorkItems
Set Title='New Title - Updated on <<FUN_NOW>>'
Where 1=1 
WITH(Query='SELECT * FROM WorkItems Where [System.TeamProject]=''MyProject'' AND ([System.Id]=6469 OR [System.Id]=6468) ')

update_workitem 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:

Update a specific work item by its Id 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE WorkItems SET
Title = ''Renamed Work Item Title'',
CustomMyField = ''New Custom Text''
WHERE Id = 7455';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Update a work item by its Id (many columns) 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE WorkItems
SET   [Title] = ''Update-QA Task <<FUN_NOW>>''
	, [Description] = ''Updated desc <<FUN_NOW>>''
  --, [WorkItemType]=''Bug'' -- Task
	, [State] = ''Active''
	, [Tags] = ''odata; api''
	, [Revision] = 1
	, [AreaPath] = ''ProductTesting\SSISPowerPack''
	, [TeamProject] = ''ProductTesting''
	, [IterationPath] = ''ProductTesting\2021.6''
	, [Reason] = ''Reactivated''
	, [AssignedTo] = ''build@mycompany.com''
	, [Priority] = 1
  --, [Triage] = ''Pending''	
  --, [StackRank] = ???
	, [Blocked] = ''No''
	, [TaskType] = ''Planned''
	, [RequiresReview] = ''No''
	, [RequiresTest] = ''No''
	, [ActivatedDate] = ''2021-01-31''
  --, [ResolvedReason] = ''Complete and Requires Review/Test''
	, [IntegrationBuild] = ''101.1.2.3''
	, [FoundIn] = ''100.1.2.3''
	, [OriginalEstimate] = 9999.123
	, [RemainingWork] = 9999.123
	, [CompletedWork] = 9999.123
	, [Size] = 9999.123
	, [Effort] = 9999.123
	, [TargetDate] = ''2021-01-31''
	, [StartDate] = ''2021-01-31''
	, [FinishDate] = ''2021-01-31''
	, [DueDate] = ''2021-01-31''
	, [StoryPoints] = 100.5
--	, [Discipline] = ''abcd''
	, [SystemInfo] = ''abcd''
	, [Steps] = ''abcd''
	, [ReproSteps] = ''abcd''
--	, [CustomFieldText] = ''abcd'' 
--	, [CustomFieldDate] = ''2021-01-31'' 
--	, [CustomFieldNumber] = 9999  
--	, [CustomFieldDecimal] = 9999.123 
--	, [CustomFieldPickListString] = ''BBB'' 
--	, [CustomFieldPickListNum] = ''2'' 
WHERE [Id] = 6455';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Add Tags to an exising workitem 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE WorkItems
SET   Tags = ''newtag1; newtag2''
WHERE Id = 6455
WITH(
	   Project=''ProductTesting''
	  ,Operation=''add''
)';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Update work items match with Wiql Query 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE WorkItems
Set Title=''New Title - Updated on <<FUN_NOW>>''
Where 1=1 
WITH(Query=''SELECT * FROM WorkItems Where [System.TeamProject]=''''MyProject'''' AND ([System.Id]=6469 OR [System.Id]=6468) '')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

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