WorkItems
Description
No description available
Parameters
Parameter | Label | Required | Options | Description | Help |
---|---|---|---|---|---|
Query | Wiql Query | YES |
|
Supported Operations
Below section contains supported CRUD operations. Each operation is executed by some EndPoint behind the scene.Method | Supported | Reference EndPoint |
---|---|---|
SELECT | query_workitems | |
INSERT | add_workitem | |
UPDATE | update_workitem | |
UPSERT | ||
DELETE | delete_workitem | |
LOOKUP | get_workitems_by_ids |
Examples
SSIS
Use Azure DevOps Connector in API Source component to read data or in API Destination component to read/write data:
Read from WorkItems table using API Source

Read/write to WorkItems table using API Destination

ODBC application
Use these SQL queries in your ODBC application data source:
Get work items for the default project(specified by connection settings)
SELECT * FROM WorkItems
Get work items for the specific project
SELECT * FROM WorkItems WITH (Project='ProductTesting')
Get a specific work item by its Id
SELECT * FROM WorkItems WHERE Id=1001
Get a specific work item by its Id, but halt the process if a 404 error is encountered
SELECT * FROM WorkItems WHERE Id='8563' WITH (ContineOn404Error='False')
Create a query to get work items by the Id of the work items in the default project for the organization
To learn more about Query syntax refer to this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops and this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/query-operators-variables?view=azure-devops
SELECT * FROM WorkItems WITH (Query='SELECT * FROM WorkItems
WHERE [System.Id] > 2000 AND [System.Id] < 7050
AND [System.TeamProject]=''ProductTesting''
AND [System.ChangedDate] < @Today
AND [System.CreatedDate] > @StartOfYear
AND [System.WorkItemType]=''Task''
AND [System.State]=''Resolved''
AND [Microsoft.VSTS.Common.Priority] >= 1
ORDER BY [System.ChangedDate] DESC
')
Create a query to get work items by the Id of the work items in the specified project for the organization
SELECT * FROM WorkItems WITH (Project='PosProject', Query='SELECT * FROM WorkItems WHERE [System.Id] = 2819')
Create a query to get work items by the types of work items in the default project for the organization
SELECT * FROM WorkItems WITH (Query='SELECT * FROM WorkItems WHERE [System.WorkItemType] = ''Task''')
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')
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'
)
Delete a specific work item by its Id
DELETE FROM WorkItems WHERE Id = 7455
Delete work items match with Wiql Query
DELETE FROM WorkItems
Where 1=1
WITH(Query='SELECT * FROM WorkItems Where [System.TeamProject]=''MyProject'' AND ([System.Id]=6469 OR [System.Id]=6468) ')
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) ')
Query work items using Wiql
This example shows how to use WIQL language to query Azure DevOps WorkItems. To learn more about WIQL Syntax you can refer this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops
SELECT
Id
, Title
, Description
, CreatedDate
, Url
FROM WorkItems
--WHERE Id=5283490 -- get just one comment for specific WorkItem Id
WITH(Query='SELECT * FROM WorkItems WHERE [System.TeamProject]=''ProductTesting'' and [System.Id]=6455 ORDER BY [System.Id] DESC')
Query work items using Wiql (with #DirectSQL Tag)
This example shows how to use WIQL language to query Azure DevOps WorkItems using #DirectSQL pre processor to invoke server side query engine. This one is same as previous example except simplified SQL. To learn more about WIQL Syntax you can refer this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops
#DirectSQL SELECT * FROM WorkItems WHERE [System.TeamProject]=''ProductTesting'' and [System.Id]=6455 ORDER BY [System.Id] DESC')
Query Sprint Hours by Team Member - Esimated vs Actual Completed
This query shows how to query total time spent by team members during a specific Sprint (i.e. Iteration Path). This query only works if your team is updating Original Estimate and Completed Hours on work item after they are done with the task.
SELECT
IterationPath as Sprint
, AssignedToUniqueName as AssignedTo
, SUM(OriginalEstimate) as OriginalHours_Total
, SUM(RemainingWork) as RemainingWork_Total
, SUM(CompletedWork) as CompletedHours_Total
FROM WorkItems
GROUP BY IterationPath,AssignedToUniqueName
WITH(
Project='ProductTesting'
--//On Preview UI Change [Max Rows] to use more sample rows - else it will use only 100 rows
,Query='SELECT * FROM WorkItems WHERE [System.IterationPath] UNDER ''ProductTesting\Sprint 1'' and State IN(''Resolved'', ''Closed'')'
--Use below to query all sprints for this Product
--,Query='SELECT * FROM WorkItems WHERE [System.IterationPath] UNDER ''ProductTesting\'' and State IN(''Resolved'', ''Closed'')'
)
Read more than 20000 work items (UNION ALL)
By default Azure DevOps API returns max 20000 items. If you want more than 20000 items then you can try below technique. Run enire query at once. Basically it creates many temp table each consisting 20000 rows each and then in final query we do UNION ALL
SELECT * INTO #t1 FROM WorkItems WITH(Project='ProductTesting', Query='SELECT * FROM WorkItems WHERE [System.Id]>=1 and [System.Id]<20000');
SELECT * INTO #t2 FROM WorkItems WITH(Project='ProductTesting', Query='SELECT * FROM WorkItems WHERE [System.Id]>=20000 and [System.Id]<40000');
SELECT * INTO #t3 FROM WorkItems WITH(Project='ProductTesting', Query='SELECT * FROM WorkItems WHERE [System.Id]>=40000 and [System.Id]<60000');
--//....
--//add more temp tables above and update UNION ALL too
SELECT * FROM #t1
UNION ALL
SELECT * FROM #t2
UNION ALL
SELECT * FROM #t3
;
SQL Server
Use these SQL queries in SQL Server after you create a data source in Data Gateway:
Get work items for the default project(specified by connection settings)
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems';
EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];
Get work items for the specific project
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WITH (Project=''ProductTesting'')';
EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];
Get a specific work item by its Id
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WHERE Id=1001';
EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];
Get a specific work item by its Id, but halt the process if a 404 error is encountered
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WHERE Id=''8563'' WITH (ContineOn404Error=''False'')';
EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];
Create a query to get work items by the Id of the work items in the default project for the organization
To learn more about Query syntax refer to this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops and this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/query-operators-variables?view=azure-devops
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WITH (Query=''SELECT * FROM WorkItems
WHERE [System.Id] > 2000 AND [System.Id] < 7050
AND [System.TeamProject]=''''ProductTesting''''
AND [System.ChangedDate] < @Today
AND [System.CreatedDate] > @StartOfYear
AND [System.WorkItemType]=''''Task''''
AND [System.State]=''''Resolved''''
AND [Microsoft.VSTS.Common.Priority] >= 1
ORDER BY [System.ChangedDate] DESC
'')';
EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];
Create a query to get work items by the Id of the work items in the specified project for the organization
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WITH (Project=''PosProject'', Query=''SELECT * FROM WorkItems WHERE [System.Id] = 2819'')';
EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];
Create a query to get work items by the types of work items in the default project for the organization
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WITH (Query=''SELECT * FROM WorkItems WHERE [System.WorkItemType] = ''''Task'''''')';
EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_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];
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];
Delete a specific work item by its Id
DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM WorkItems WHERE Id = 7455';
EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];
Delete work items match with Wiql Query
DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM WorkItems
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 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];
Query work items using Wiql
This example shows how to use WIQL language to query Azure DevOps WorkItems. To learn more about WIQL Syntax you can refer this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT
Id
, Title
, Description
, CreatedDate
, Url
FROM WorkItems
--WHERE Id=5283490 -- get just one comment for specific WorkItem Id
WITH(Query=''SELECT * 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 work items using Wiql (with #DirectSQL Tag)
This example shows how to use WIQL language to query Azure DevOps WorkItems using #DirectSQL pre processor to invoke server side query engine. This one is same as previous example except simplified SQL. To learn more about WIQL Syntax you can refer this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops
DECLARE @MyQuery NVARCHAR(MAX) = '#DirectSQL SELECT * 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 Sprint Hours by Team Member - Esimated vs Actual Completed
This query shows how to query total time spent by team members during a specific Sprint (i.e. Iteration Path). This query only works if your team is updating Original Estimate and Completed Hours on work item after they are done with the task.
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT
IterationPath as Sprint
, AssignedToUniqueName as AssignedTo
, SUM(OriginalEstimate) as OriginalHours_Total
, SUM(RemainingWork) as RemainingWork_Total
, SUM(CompletedWork) as CompletedHours_Total
FROM WorkItems
GROUP BY IterationPath,AssignedToUniqueName
WITH(
Project=''ProductTesting''
--//On Preview UI Change [Max Rows] to use more sample rows - else it will use only 100 rows
,Query=''SELECT * FROM WorkItems WHERE [System.IterationPath] UNDER ''''ProductTesting\Sprint 1'''' and State IN(''''Resolved'''', ''''Closed'''')''
--Use below to query all sprints for this Product
--,Query=''SELECT * FROM WorkItems WHERE [System.IterationPath] UNDER ''''ProductTesting\'''' and State IN(''''Resolved'''', ''''Closed'''')''
)';
EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];
Read more than 20000 work items (UNION ALL)
By default Azure DevOps API returns max 20000 items. If you want more than 20000 items then you can try below technique. Run enire query at once. Basically it creates many temp table each consisting 20000 rows each and then in final query we do UNION ALL
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * INTO #t1 FROM WorkItems WITH(Project=''ProductTesting'', Query=''SELECT * FROM WorkItems WHERE [System.Id]>=1 and [System.Id]<20000'');
SELECT * INTO #t2 FROM WorkItems WITH(Project=''ProductTesting'', Query=''SELECT * FROM WorkItems WHERE [System.Id]>=20000 and [System.Id]<40000'');
SELECT * INTO #t3 FROM WorkItems WITH(Project=''ProductTesting'', Query=''SELECT * FROM WorkItems WHERE [System.Id]>=40000 and [System.Id]<60000'');
--//....
--//add more temp tables above and update UNION ALL too
SELECT * FROM #t1
UNION ALL
SELECT * FROM #t2
UNION ALL
SELECT * FROM #t3
;';
EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];