A common scenario I encounter is when a client has some existing assets in TFS or VSTS and they want a clean slate, to consolidate backlogs or to use a new Process Template. More recently it is because they want to move their backlog to Visual Studio Team Services (formerly Visual Studio Online)

There are other open source tools for migrating Work Items out there such as Total TFS Migration but they are not always maintained and there are some commercial tools which can be expensive.

If the situation is right then I often find Excel is the simplest mechanism to copy Work Items from one Team Project to another. I have also used the same technique to successfully move items from Jira, Trello and other systems.

However, using an Excel input list is not a perfect solution on its own. Off the top of my head, you’re going to have to consider:

New Work Item IDs

Work Item Links

Creation State

Hyperlinks

Attachments

Lost Work Item history

Discussions

Test Steps

Created Date

Closed Date

Area Paths

Iteration Paths

We’ll look at some of those later in this post but let’s get some Work Items copied to begin with.

The simplest scenario is if both source and target projects use the same Process Template so that all your fields match (eg. copy the value of Microsoft.VSTS.Scheduling.Effort from source to target).

If the templates don’t match then you’ll have to do some planning to understand how the fields match (for example Microsoft.VSTS.Scheduling.StoryPoints to Microsoft.VSTS.Scheduling.Effort) or fields that might be missing (eg. Microsoft.VSTS.Scheduling.CompletedWork or Microsoft.VSTS.Scheduling.OriginalEstimate). If the fields are missing then you could consider adding them to the target process template, storing the information somewhere else or simply leave it behind.

Create a Query

First up we need a query to return all the items you want to copy.

Ask yourself, are you going to copy closed, done or removed Work Items. Often clients do but don’t realise that it’s not going to give them the historical view that they expect as the Closed Dates will be different. I’d encourage you to leave them behind and retain the old project for a while.

If you have any links between Work Items that you wish to preserve then it needs to be a Tree Query. We could hook up links with PowerShell later on (mental note to blog about that later) but I like to use Excel if possible.

A query like this is going to bring back everything in a hierarchical structure regardless of the State or Work Item type.

Something like this will omit Done or Removed Work Items but just be careful that you don’t lose orphaned items or perhaps an odd situation where you have active child items whose parents have been moved to Done.

You may need to play around with the query to get what you want.

Importantly, click on Column Options and choose which fields you wish to import.

Then choose your sort order. Backlog Priority followed by ID is probably a good shout.

Copy your Query to the target project

We have our query to export the data we want but now we need to add that to the target project so that we can import the data.

Open the source project in Visual Studio/Team Explorer and navigate to your query.

Click on Edit Query

And Select File-> Save As…

We want to save our query to a file (*.wiq), somewhere we we can find it. Open the file in a text editor of your choice (I like Notepad++) or you can do it in Visual Studio as long as you select the Open With drop down.

Edit the Server URL and the Team Project Name in the wiq file and save it again.

Now open Visual Studio and connect to your target team Project.

Select File->Open->File…

Navigate to your *.wiq query file.

Select Save As…

and select your new project as the target server.

The query should now be available in the target Team Project.

Open your Query in Excel

Open Excel, navigate to the Team tab, hit New List and select your query in the source Team Project.

Open another instance of Excel (hold down the Shift key when you click the shortcut) and open the query in the target Team Project.

In the source project we can see the Work Items we are going to migrate. Note that the Parent/Child hierarchy is represented by multiple Title columns.

This hierarchy is missing on the target query so we need to add as many levels as we need.

On the Team ribbon, select Add Tree Level, in my case I needed three more levels to my hierarchy to represent Epic-Feature-PBI/Bug-Task.

Create new columns

Now we need to create some space where we can store related information that we will not publish to the new team project. You could add custom fields in TFS to store this information but I like to keep the template as clean as possible.

Right click on the column in Excel and Insert columns where you need them.

Repeat the process to store things like the Old Id, the Old State (all new items will be created as New/To Do etc), Old Area Path and so on.

I like to colour these columns (it’s always orange for some reason) so that it is clear that they will not be published back to TFS and only live on this spreadsheet.

Clicking Choose Columns in the Team ribbon allows you to re-order the columns including the non-TFS ones. This will make it easier to copy and past in the next section.

Copy source Project data

Begin the process of copying the data from the source sheet to the target sheet. When we hit Publish on the Team menu, new Work Item IDs will be generated for us in the new project and the Parent-Child links will be created.

Edit the copied data

Now we need to alter some things like the Work Item State. The TFS Process Template will not allow us to create Work Items in a non-starting state so our items will be New or To Do for example. We need to filter on the Old States we want to edit. Click the drop down on the column header and select the first state to edit.

Now we see only the items we need to change. Change and then fill down the value in the State column (In Progress in the example below) and publish back to TFS. Repeat the process for all the states returned in your query

You will also need to come back and do this for things like Area Path and Iteration Path if you haven’t already created them in your new project. Just save this spreadsheet and you can refresh and publish from/to TFS anytime.

Conclusion

So now we have our basic data in our new Team Project along with the Work Item hierarchy. For many this will be enough but for others, there will be hyperlinks to SharePoint documents, attachments and more that need to be migrated.