Merging team projects: Restoring the Work Item links

This has been the biggest issue of the migration: I knew how to migrate the Work Items, but I knew also that links between work items would only be *partially* migrated. I’ve explained the problem in this post. The only solution I could find was to write a tool myself to recreate the work item links in the destination Team Project.

What links exactly are migrated by default ?


Those links are URLs, they are static and they are migrated by default.


Changeset links are implemented in TFS with “External links”, there is no much to worry about them as long as you migrate onto the same TFS server, and that was my case.

Related links

These are links between work items themselves. Here is a schema that shows the partial links migration:


The TFS Integration Platform will only migrate links that are “contained” in the source Work Item query, and for each link, both concerned work items need to be included in the query.

Migration by range of work items

Because it was not possible in my case to perform the migration in one single query, I adopted a *chunk* strategy based on time, or, more exactly, based on IDs, which is actually the same.

The source query was like : get all work items from all the source team projects where the ID is ranging from XXX to YYY

An important thing is that the TFS Integration tools are idempotent: you may launch the same migration twice, it would not duplicate what’s already been migrated, but it detects and takes anything that is new. It just works, cool!

Time considerations

There was more than 120.000 work items to migrate, and I needed to plan the migration timing right, that is why starting the real migration several weeks before the dead line had a great value :

  • I got real performance feedback that I could use to calculate the global migration time needed
  • I was sure about what I was getting, limiting the risks as time was passing, because the job was getting done from week to week

A tool to restore links after the migration

What I like very much with TFS is its API. You can do magic with it. Yeah sometimes I complain that a particular feature is missing in the base product, yet to realize that it can be easily done by using the API. It’s just .NET programming, and with a few lines of code you can access Work Items, Source control, builds, and more.Though, the community around this API is not very big, and even if support forums are great, I find it lacks documentation, and this is where you’ll be happy to find cool guys like Shai Raiten and his TFS API blog posts series, great place to get started.

Continue reading

[TFS 2010] How to get progress reports from a hierarchical Work Items structure – Part 4

[Code and executable of the utility is attached]

We’ve had a brief look lately at how we could use Excel with Analysis Services to summarize a tree hierarchy of work item data to get a project progress report. Selecting the top work item of a tree is not always the easiest approach. Today I’d like to share with you another solution I’ve used with success.

Top-down propagation of work items context

Another strategy is to propagate the parents values of work items in every work item of their hierarchy. This can be achieved using the new server-side plugin architecture in TFS 2010, but I only implemented it with regular TFS client API. By spreading out, eg the project title, and the feature name in all sublevel work items, we’ll be able to very easily establish filters that will select only the work items of our hierarchy. Those filters can be applied to flat queries, as well as to hierarchical ones.

So the best is to create new Work item fields, let’s says a String field named MyCompany.Common.ProjectName, in all your work item types. Don’t forget to make this field a dimension for later use in the cube.


For this you need a batch process: some utility that will scan recently touched work items and that will ensure that every single work item from a hierarchy contains the top work item value.

The solution

This small utility is a command line utility that you can use to propagate any String field within any WI hierarchy.

Usage is:

TfsTreePropagation.exe <Team Project> <GUID of the WI Query in TFS> <Field to propagate name>

You can get the GUID of a query by displaying it’s properties in Visua Studio. The query can be either flat, or tree. Top Work items will be considered as root of every Parent-Child hierarchy, and propagation will start independently for each of these top work items. Hierarchical queries are particularly useful to find out recently modified work items (just add a date filter among sublevel filters).

Only mismatched values in found work items will be searched and updated (the rest are of course untouched). So we can consider queries inside each hierarchy are optimized to only look for mismatched values (there is actually only one query to do this).

If you need to propagate multiple fields, or within different scope, simply call the tool multiple times in a row.

I advise using scheduled tasks to run this utility. You may create a batch file as follows:

TfsTreePropagation.exe MyTeamProject 3249ee07-e8bd-48b4-8769-98ab1a31b551 MyCompany.MyProjectField >> TfsTreePropagation-MyProjectField.log
TfsTreePropagation.exe MyTeamProject 91dcc376-1ea1-41d3-a4d6-ad0fd59917c7 MyCompany.MyOtherField >> TfsTreePropagation-MyOtherField.log

I’ve attached here the code and the executable.


Now you can filter with the MyCompany.Common.ProjectName field in your Analysis Service report. Since it is a dimension, the performance is just blazing. You can still benefit from the hierarchical display from Analysis Services PivotTable by selecting the Work Item Tree Hierarchy, but the filter won’t reside in this field any longer.


I’ve been using this utility with success, it just requires for you to check the log sometimes, otherwise it is pretty stable. Hope you’ll find it useful, and remember I’m always interested in getting feedback!

[TFS 2010] How to get progress reports from a hierarchical Work Items structure – Part 3

After having explored TFS work item connection from Excel, let’s have a look at what we can get from the TFS cube. All TFS significant data is gathered into a warehouse, which in turn is the data source of an OLAP cube stored in the Tfs_Analysis database. Therefore, when I use a hierarchical Work Item organization, will I be able to get easily nice reports ?

Using the TFS cube from Excel

The good news is that the cube takes the hierarchical links into account, so basically: yes! But if you have a very big work item database, this might be tedious and impractical. Another bad point is that calculated fields doesn’t work by default in Excel 2010 when the PivotTable is connected to SQL Server Analysis Services.

If your not familiar at all with the TFS cube and how to connect to it from Excel, have look at these fantastic tutorials, and this introduction as well.

Setting up a quick report

Now, what you get from the cube can look similar to our previously established report:


The main difference is the parent-child hierarchy is accessible through a dedicated Work Item Tree Hierarchy dimension. I’ve been able to set up this report very quickly by selecting the root of my hierarchy:




Problems with this solution

  1. First: if you have a very big database, menus are very very slow, to the point that it is impractical to select your top project. The data is sent on the wire between the MSSQL AS server and your local Excel, so, have it a try to get an idea of the responsiveness in your conditions!
  2. Calculated fields won’t work! Don’t panic, it’s a limitation that can be overridden, simply install the OLAP PivotTable Extensions and hurray, they will be available (get details about this here). But remember your PivotTable is connected to a multidimensional data source (this not a simple array), and your calculated fields will be more complex expressions (you may have to deal with MDX)! That makes it a bit harder than simply referencing columns.
  3. Finally, you should consider security, and the warehouse and analysis database are an all or nothing thing, and also require direct access to the SQL Server. You’ll grant people read access to those database, and they’ll be able to access all projects data. That is why by default, no one can access the cube apart from the reports, which can’t be modified. There is no security flaw by default of course, just consider you’re giving access to the whole data this way, reports designer can see everything. On the other hand, accessing work items from Excel is using regular TFS web service access, and uses the regular permissions present on Area nodes, which is much better IMHO.


If you manage to get the hierarchy you want in the PivotTable, then you’re done, and you don’t have to mess up with adding columns in the TFS extracted work items table. But there other solutions I’d like to talk about, stay tuned.