TFS customization - Team Foundation Server question

Question

We have a process where when the developers change or add a database script and check it into the project. At the time of deployment the release manager needs to know what work items have database script checked in against it. Is there a way where we can query or create a custom report in TFS to get a list of work items that have a file in a changeset with a particular file extension (.sql). This way the release manager will get a list of the work items that she can then give to the DBAs to analyse, check and apply to the server.

We are using TFS 2008.

Answer 1

I'm not a report writer, but I assume that you could probably generate a custom report off of the tfswarehouse where you could filter on the extension. But that is just an assumption.

Perhaps you could write a custom checkin policy that sends an email to the appropriate folks based on file extension. This seems like a very heavy and inappropriate way to utilize policies, but it would probably work.

just some thoughts with no verifiable data to back them up. :D

Answer 2

You can tap into the build events and have it point to your custom web service that could inspect the list of changesets applied. This could in turn do a lookup to TFS to get the file names and then could spawn work items. This would imply knowledge of the TFS APIs, but they are pretty self explanatory. The events are configured via the Alerts editor which I believe is part of the TFS Power Tools. I have something like this now that aggregates Completed work and updates our external time tracking system.

Answer 3

Have you looked at the new GDR R2 Database project (a free download for team editions of Visual Studio 2008)?

This type of project was originally part of the database edition of Visual Studio.

It has the ability to import a schema from an existing database, perform schema compares, support multiple targets (System Test, UAT, Production, etc), perform TSQL static code analysis, and produce standalone deployment artifacts.

Answer 4

Install the latest Power Tools. Then you can run this quick Powershell script:

Get-TfsItemHistory $/project/*.sql -r -version D6/1/2009~ | 
    %{ $_.workitems } | %{ $_.id } | select -unique | sort

(adjust the project name and the date as needed)

While this is great for human-assisted code reviews, I would strongly caution against using this to actually construct the deployment script. If a developer forgot to associate his checkin with a work item, or if he made previous checkins to SQL files that his bugfixes depend on, you will be rolling out an inconsistent set of changes. It's always best to make the deploy script you use in your test environment match your production deployment as closely as possible.