1

Where are the relationships between Work Items and Build/Release stored in the Team Foundation Server 2015 (Update 2.1) database?

The Build and Release APIs, as per their documentation, can query for any Work Items that relate to a Build/Release as follows:

http://tfsservername:8080/tfs/collectionname/Sandbox/_apis/build/builds/2872/workitems
http://tfsservername:8080/tfs/collectionname/Sandbox/_apis/Release/releases/19/workitem

The WorkItem API has no such call available.

Background info only: Currently Work Items in TFS 2015 Update 2.1 have no option to 'Add Link' to a Release - this makes Work Item deployment tracking very difficult for project managers, managing tens of builds/releases on tens of projects. I'd like to reverse the query used by the above API calls, to add this functionality.

ManxJason
  • 928
  • 12
  • 33

1 Answers1

1

Currently, neither the WorkItem, Release nor Build APIs include any calls to locate a Release based on WorkItems.

Likewise this information doesn't exist in the Tfs_Warehouse database (created by enabling TFS Reporting/Analysis), in fact at the time of writing this, there's no 'Release' information in there at all.

In order to find Releases by a Work Item, you can use the Team Project database to access this information. Although no direct relationship exists, you can reverse engineer the various stored procedures which are called by TFS when you load-up a release (WorkItems are displayed at this point):

DECLARE @workItem int;

SET @workItem = 751;

SELECT *
FROM Release.tbl_Release AS r
JOIN Release.tbl_ReleaseArtifactSource AS a ON a.ReleaseId = r.Id
JOIN Build.tbl_Change AS c ON c.BuildId = a.ArtifactVersionId
JOIN tbl_Mention AS m ON m.SourceId LIKE CONCAT('%', c.Descriptor, '%') -- Guid
WHERE m.ArtifactId = @workItem;

You can then utlitze this query by via Web API or by programmatically injecting as a WorkItem Team Query, and calling via TFS Extension.

ManxJason
  • 928
  • 12
  • 33