Azure DevOps Connector
Documentation
Version: 2
Documentation
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 Label Required Options Description
Project Project Name YES The Name of the project.
Id Work Item Id NO
Operation Operation NO
Option Value
replace replace
add add
remove remove
copy copy
move move
test test
Some operation requires you to supply 'add' rather than 'replace' (e.g. adding new tag)

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
If the column you are looking for is missing, consider customizing Azure DevOps Connector.

Input Columns

Label Data Type (SSIS) Data Type (SQL) Length Raw Description
Id DT_WSTR nvarchar(4000) 4000 False
WorkItemType DT_WSTR nvarchar(1000) 1000 True
Title DT_WSTR nvarchar(1000) 1000 True
Description DT_WSTR nvarchar(4000) 4000 True
State DT_WSTR nvarchar(100) 100 True
History DT_WSTR nvarchar(100) 100 True
Tags DT_WSTR nvarchar(4000) 4000 True
Revision DT_I4 int True
AreaPath DT_WSTR nvarchar(500) 500 True
TeamProject DT_WSTR nvarchar(500) 500 True
IterationPath DT_WSTR nvarchar(500) 500 True
Reason DT_WSTR nvarchar(500) 500 True
AssignedTo DT_WSTR nvarchar(500) 500 True
Priority DT_I4 int True
Triage DT_WSTR nvarchar(1000) 1000 True
StackRank DT_I4 int True
Blocked DT_WSTR nvarchar(500) 500 True
TaskType DT_WSTR nvarchar(500) 500 True
RequiresReview DT_WSTR nvarchar(100) 100 True
RequiresTest DT_WSTR nvarchar(100) 100 True
ActivatedDate DT_DBTIMESTAMP datetime True
ActivatedBy DT_WSTR nvarchar(500) 500 True
ClosedDate DT_DBTIMESTAMP datetime True
ClosedBy DT_WSTR nvarchar(500) 500 True
ResolvedDate DT_DBTIMESTAMP datetime True
ResolvedReason DT_WSTR nvarchar(4000) 4000 True
IntegrationBuild DT_WSTR nvarchar(500) 500 True
FoundIn DT_WSTR nvarchar(500) 500 True
OriginalEstimate DT_R4 real True
RemainingWork DT_R4 real True
CompletedWork DT_R4 real True
Size DT_R4 real True
Effort DT_R4 real True
TargetDate DT_DBTIMESTAMP datetime True
StartDate DT_DBTIMESTAMP datetime True
FinishDate DT_DBTIMESTAMP datetime True
DueDate DT_DBTIMESTAMP datetime True
StoryPoints DT_R4 real True
Discipline DT_WSTR nvarchar(100) 100 True
SystemInfo DT_WSTR nvarchar(4000) 4000 True
Steps DT_WSTR nvarchar(4000) 4000 True
ReproSteps DT_WSTR nvarchar(4000) 4000 True
[$parent.referenceName$] DT_WSTR nvarchar(4000) 4000 True
Required columns that you need to supply are bolded.

Examples

SSIS

Use Azure DevOps Connector in API Source component to read data or in API Destination component to read/write data:

Update rows 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:

Azure DevOps
WorkItems
Update
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).