Create Work Item
Name
add_workitem
Description
Creates a new work item in the default project within the organization. [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 |
---|---|---|---|---|---|
WorkItemType |
DT_WSTR
|
nvarchar(4000)
|
4000 | False |
|
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 |
Examples
SSIS
Use Azure DevOps Connector in API Source component to read data or in API Destination component to read/write data:
Insert into 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:
Create a new work item in the default project for the organization
INSERT INTO WorkItems (WorkItemType, Title, [CustomFieldText], [CustomFieldDate], [CustomFieldNumber])
VALUES ('task', 'Fix Issues in System', 'My Custom Text', '2021-01-01T23:59:59.123', 1000)
Create a new work item in a specific project
INSERT INTO WorkItems (WorkItemType, Title, [CustomFieldText], [CustomFieldDate], [CustomFieldNumber])
VALUES ('task', 'Fix Issues in System', 'My Custom Text', '2021-01-01T23:59:59.123', 1000)
WITH(Project='MyProject')
Create a new work with many fields in a specific project
INSERT INTO WorkItems (
[WorkItemType]
, [Title]
, [Description]
, [Tags]
, [AreaPath]
, [IterationPath]
, [AssignedTo]
, [Priority]
, [Triage]
--, [StackRank]
, [Blocked]
, [TaskType]
, [RequiresReview]
, [RequiresTest]
, [IntegrationBuild]
, [FoundIn]
, [OriginalEstimate]
, [RemainingWork]
, [CompletedWork]
, [Size]
, [Effort]
, [TargetDate]
, [StartDate]
, [FinishDate]
, [DueDate]
, [StoryPoints]
--, [Discipline]
, [SystemInfo]
, [Steps]
, [ReproSteps]
--, [CustomFieldText] --custom field prefix with CustomYOUR_FIELD_NAME_WITHOUT_SPACE_OR_DASH_OR_DOT
--, [CustomFieldDate]
--, [CustomFieldNumber]
--, [CustomFieldDecimal]
--, [CustomFieldPickListString]
--, [CustomFieldPickListNum]
)
VALUES (
'Bug' -- Task, User Story -- WorkItemType
, 'My Task Title - Created on <<FUN_NOW>>' --Title
, 'Some Description - Created on <<FUN_NOW>>' --Description
, 'odata; api' --Tags
, 'ProductTesting\SSISPowerPack' --AreaPath
, 'ProductTesting\2021.6' --IterationPath
, 'build@mycompany.com' --AssignedTo
, 1 --Priority
, 'Pending' --Triage
--, ??? --[StackRank]
, 'No' --Blocked
, 'Planned' --TaskType
, 'No' --RequiresReview
, 'No' --RequiresTest
, '101.1.2.3' --IntegrationBuild
, '100.1.2.3' --FoundIn
, 9999.111 --OriginalEstimate
, 9999.222 --RemainingWork
, 9999.333 --CompletedWork
, 9999.444 --Size
, 9999.555 --Effort
, '2021-01-31' --TargetDate
, '2022-01-31' --StartDate
, '2023-01-31' --FinishDate
, '2024-01-31' --DueDate
, 100.5 --StoryPoints
--, 'Some Discipline' --Discipline
, 'Windows 10' --SystemInfo
, 'Here are steps' --Steps
, 'Here are steps to reproduce' --ReproSteps
--, 'abcd' --CustomFieldText
--, '2021-01-31' --CustomFieldDate
--, 9999 --CustomFieldNumber
--, 9999.123 --CustomFieldDecimal
--, 'BBB' --CustomFieldPickListString
--, '2' --CustomFieldPickListNum
)
WITH(Project='ProductTesting')
add_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:
Create a new work item in the default project for the organization
DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO WorkItems (WorkItemType, Title, [CustomFieldText], [CustomFieldDate], [CustomFieldNumber])
VALUES (''task'', ''Fix Issues in System'', ''My Custom Text'', ''2021-01-01T23:59:59.123'', 1000)';
EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];
Create a new work item in a specific project
DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO WorkItems (WorkItemType, Title, [CustomFieldText], [CustomFieldDate], [CustomFieldNumber])
VALUES (''task'', ''Fix Issues in System'', ''My Custom Text'', ''2021-01-01T23:59:59.123'', 1000)
WITH(Project=''MyProject'')';
EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];
Create a new work with many fields in a specific project
DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO WorkItems (
[WorkItemType]
, [Title]
, [Description]
, [Tags]
, [AreaPath]
, [IterationPath]
, [AssignedTo]
, [Priority]
, [Triage]
--, [StackRank]
, [Blocked]
, [TaskType]
, [RequiresReview]
, [RequiresTest]
, [IntegrationBuild]
, [FoundIn]
, [OriginalEstimate]
, [RemainingWork]
, [CompletedWork]
, [Size]
, [Effort]
, [TargetDate]
, [StartDate]
, [FinishDate]
, [DueDate]
, [StoryPoints]
--, [Discipline]
, [SystemInfo]
, [Steps]
, [ReproSteps]
--, [CustomFieldText] --custom field prefix with CustomYOUR_FIELD_NAME_WITHOUT_SPACE_OR_DASH_OR_DOT
--, [CustomFieldDate]
--, [CustomFieldNumber]
--, [CustomFieldDecimal]
--, [CustomFieldPickListString]
--, [CustomFieldPickListNum]
)
VALUES (
''Bug'' -- Task, User Story -- WorkItemType
, ''My Task Title - Created on <<FUN_NOW>>'' --Title
, ''Some Description - Created on <<FUN_NOW>>'' --Description
, ''odata; api'' --Tags
, ''ProductTesting\SSISPowerPack'' --AreaPath
, ''ProductTesting\2021.6'' --IterationPath
, ''build@mycompany.com'' --AssignedTo
, 1 --Priority
, ''Pending'' --Triage
--, ??? --[StackRank]
, ''No'' --Blocked
, ''Planned'' --TaskType
, ''No'' --RequiresReview
, ''No'' --RequiresTest
, ''101.1.2.3'' --IntegrationBuild
, ''100.1.2.3'' --FoundIn
, 9999.111 --OriginalEstimate
, 9999.222 --RemainingWork
, 9999.333 --CompletedWork
, 9999.444 --Size
, 9999.555 --Effort
, ''2021-01-31'' --TargetDate
, ''2022-01-31'' --StartDate
, ''2023-01-31'' --FinishDate
, ''2024-01-31'' --DueDate
, 100.5 --StoryPoints
--, ''Some Discipline'' --Discipline
, ''Windows 10'' --SystemInfo
, ''Here are steps'' --Steps
, ''Here are steps to reproduce'' --ReproSteps
--, ''abcd'' --CustomFieldText
--, ''2021-01-31'' --CustomFieldDate
--, 9999 --CustomFieldNumber
--, 9999.123 --CustomFieldDecimal
--, ''BBB'' --CustomFieldPickListString
--, ''2'' --CustomFieldPickListNum
)
WITH(Project=''ProductTesting'')';
EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];
add_workitem
endpoint belongs to
WorkItems
table(s), and can therefore be used via those table(s).