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
Name: BypassRules

Label: Bypass Rules (useful to set Readonly - e.g. ResolvedBy)

Allows setting read-only/system fields during creation (like CreatedBy, ResolvedBy).
Option Value
true true
false false
Name: SuppressNotifications

Label: Suppress email notifications

Suppresses email notifications triggered by the new work item creation
Option Value
true true
false false
Name: ValidateOnly

Label: Validate Only (Do not perform actual update/insert)

Validates the JSON patch without actually creating / updating the work item. Useful for testing templates.
Option Value
true true
false false

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
Id DT_WSTR nvarchar(4000) 4000
WorkItemType DT_WSTR nvarchar(1000) 1000
Title DT_WSTR nvarchar(1000) 1000
Description DT_NTEXT nvarchar(MAX)
State DT_WSTR nvarchar(100) 100
History DT_NTEXT nvarchar(MAX)
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
ResolvedBy DT_WSTR nvarchar(500) 500
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_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
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'
  --, [ResolvedBy] = 'John Smith <jsmith@mycompany.com>' --or Just Name or Email (Must set ByPassRules='true' Param to set this readonly field)
	, [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
--WITH(
--  BypassRules='true'  --Useful to Update ReadOnly Fields like ResolvedBy, ClosedBy
-- ,SuppressNotifications='true'  --Avoids email notifications on change
-- ,ValidateOnly='true'  --Dont perform actual update - just validate
--)

Add Tags to an exising workitem 

UPDATE WorkItems
SET   Tags = 'newtag1; newtag2'
WHERE Id = 6455
WITH(
	   Project='ProductTesting'
	  ,Operation='add'
   -- ,BypassRules='true'  --Useful to Update ReadOnly Fields like ResolvedBy, ClosedBy
   -- ,SuppressNotifications='true'  --Avoids email notifications on change
   -- ,ValidateOnly='true'  --Dont perform actual update - just validate	  
)

Update readonly fields (e.g. ClosedBy, ResolvedBy) 

UPDATE WorkItems
SET   [Title] = 'Update-QA Task <<FUN_NOW>>'
	, [Description] = 'Updated desc <<FUN_NOW>>'
    , [WorkItemType]='Bug' -- Task
	, [State] = 'Resolved'
	, [Tags] = 'odata; api'
	, [ResolvedBy] = 'some.one@mycompany.com' --(Must set ByPassRules='true' Param to set this readonly field)
WHERE [Id] = 9626
WITH(
  BypassRules='true'  --Useful to Update ReadOnly Fields like ResolvedBy, ClosedBy
-- ,SuppressNotifications='true'  --Avoids email notifications on change
-- ,ValidateOnly='true'  --Dont perform actual update - just validate
)

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''
  --, [ResolvedBy] = ''John Smith <jsmith@mycompany.com>'' --or Just Name or Email (Must set ByPassRules=''true'' Param to set this readonly field)
	, [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
--WITH(
--  BypassRules=''true''  --Useful to Update ReadOnly Fields like ResolvedBy, ClosedBy
-- ,SuppressNotifications=''true''  --Avoids email notifications on change
-- ,ValidateOnly=''true''  --Dont perform actual update - just validate
--)';

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''
   -- ,BypassRules=''true''  --Useful to Update ReadOnly Fields like ResolvedBy, ClosedBy
   -- ,SuppressNotifications=''true''  --Avoids email notifications on change
   -- ,ValidateOnly=''true''  --Dont perform actual update - just validate	  
)';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Update readonly fields (e.g. ClosedBy, ResolvedBy) 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE WorkItems
SET   [Title] = ''Update-QA Task <<FUN_NOW>>''
	, [Description] = ''Updated desc <<FUN_NOW>>''
    , [WorkItemType]=''Bug'' -- Task
	, [State] = ''Resolved''
	, [Tags] = ''odata; api''
	, [ResolvedBy] = ''some.one@mycompany.com'' --(Must set ByPassRules=''true'' Param to set this readonly field)
WHERE [Id] = 9626
WITH(
  BypassRules=''true''  --Useful to Update ReadOnly Fields like ResolvedBy, ClosedBy
-- ,SuppressNotifications=''true''  --Avoids email notifications on change
-- ,ValidateOnly=''true''  --Dont perform actual update - just validate
)';

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