Azure DevOps Connector
Documentation
Version: 2
Documentation
Endpoint

Query Work Item Comments

Name

query_workitem_comments

Description

Get work item comments associated with the specified project and organization that are filtered by a Wiql query. (A team can optionally be specified as well.). Read more about this API here https://learn.microsoft.com/en-us/rest/api/azure/devops/wit/wiql/query-by-wiql?view=azure-devops-rest-7.0 and here https://learn.microsoft.com/en-us/azure/devops/boards/queries/query-operators-variables?view=azure-devops [API reference]

Related Tables

WorkItemComments

Parameters

Parameter Label Required Options Description
Project Project Name YES The Name of the project.
Query Wiql Query YES The WIQL query (refer Azure DevOps Help to learn Wiql syntax. [API reference]
Team Team Id or Name NO Id or name of the team associated with the query.
TimePrecision Use time precision NO
Option Value
false false
true true
Whether or not to use time precision.

Output Columns

Label Data Type (SSIS) Data Type (SQL) Length Raw Description
Id DT_I4 int False
WorkItemId DT_I4 int False
Text DT_NTEXT nvarchar(MAX) False
RenderedText DT_NTEXT nvarchar(MAX) False
Format DT_WSTR nvarchar(4000) 4000 False
CreatedDate DT_DBTIMESTAMP datetime False
CreatedByUniqueName DT_WSTR nvarchar(4000) 4000 False
CreatedById DT_WSTR nvarchar(4000) 4000 False
CreatedByDisplayName DT_WSTR nvarchar(4000) 4000 False
ModifiedDate DT_DBTIMESTAMP datetime False
ModifiedByUniqueName DT_WSTR nvarchar(4000) 4000 False
ModifiedById DT_WSTR nvarchar(4000) 4000 False
ModifiedByDisplayName DT_WSTR nvarchar(4000) 4000 False
Url DT_WSTR nvarchar(1000) 1000 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
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:

Read from WorkItemComments table using API Destination

This Endpoint belongs to WorkItemComments table, therefore you cannot work with it directly. Use this table and table-operation pair instead:

Azure DevOps
WorkItemComments
Select
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Query work item comments 

SELECT * FROM query_workitem_comments
WITH
(
    "Project" = 'abcd-1234-project',
    "Query" = 'SELECT * FROM WorkItems Order By [System.Id] DESC'
)

Get all comments for all work items 

SELECT * FROM WorkItemComments

Query work item comments using Wiql 

SELECT 
	  Id
	, WorkItemId
	, Text
	, RenderedText
	, Format
	, CreatedDate
	, CreatedByUniqueName
	, CreatedById
	, CreatedByDisplayName
	, ModifiedDate
	, ModifiedByUniqueName
	, ModifiedById
	, ModifiedByDisplayName
	, Url
FROM WorkItemComments 
--WHERE Id=5283490 -- get just one comment for specific WorkItem Id
WITH(Query='SELECT [System.Id] FROM WorkItems WHERE [System.TeamProject]=''ProductTesting'' and [System.Id]=6455 ORDER BY [System.Id] DESC')

query_workitem_comments endpoint belongs to WorkItemComments 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:

Query work item comments 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM query_workitem_comments
WITH
(
    "Project" = ''abcd-1234-project'',
    "Query" = ''SELECT * FROM WorkItems Order By [System.Id] DESC''
)';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Get all comments for all work items 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItemComments';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Query work item comments using Wiql 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT 
	  Id
	, WorkItemId
	, Text
	, RenderedText
	, Format
	, CreatedDate
	, CreatedByUniqueName
	, CreatedById
	, CreatedByDisplayName
	, ModifiedDate
	, ModifiedByUniqueName
	, ModifiedById
	, ModifiedByDisplayName
	, Url
FROM WorkItemComments 
--WHERE Id=5283490 -- get just one comment for specific WorkItem Id
WITH(Query=''SELECT [System.Id] FROM WorkItems WHERE [System.TeamProject]=''''ProductTesting'''' and [System.Id]=6455 ORDER BY [System.Id] DESC'')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

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