Yes, You Can Move TFS WorkItems Between Projects! But, your TFS Warehousing will break, and Microsoft Support will no longer talk to you.

0
41

Introduction

Since its inception, to date, Microsoft has failed to provide a satisfactory mechanism to move WorkItems from one TFS Project to another.

In mid 2010, Lang Hsieh posted this approach and rational to moving TFS WorkItems.

Independently, in mid 2015, having similar needs to move a large number of TFS WorkItems without losing the history, assignments, links and other associations, and being unaware of Lang Hsieh’s work, I performed similar research, resulting in this CodeProject article and a convenience utility to reduce human error.

In the comments below you will see two self-proclaimed TFS Program Managers advising against the use of this approach, with the ‘loss of support’ statement from Microsoft being verified earlier by a real CodeProject contributor. The most recent comment requests that this article be deleted from CodeProject. Though I take their objections seriously, the best resolution would be for Microsoft to provide adequate native support for the movement of WorkItems. I need to manage my team and their project communications and assignments with cohesion and fluidity. Thus I esteem the future upgrade headache, with the fallback of a migration less of an issue than the team disruptions caused by the loss of WorkItem associations.

I depend upon this roguish ability to move TFS WorkItems between TFS Projects to manage my team of product developers without the loss of change history or attachments. But I do not have a support contract to lose and do not plan to upgrade soon. And other than the need to modify the state or assignment fields, from the TFS UI, when the state or assignment is not a member of the new project’s pool, I do not experience any harmful operational effects.

Obviously, this approach is not for everyone. But if you also have this need, and can’t wait for the TFS team to complete their implementation, … Start with the Caveats and read on.

Caveats

Use at your own risk. In the comments below, self-purported Microsoft Program Managers for TFS strongly object to this approach and illude to serious consequences. (Note: maintaining a backup of your database files and TFS servers is advisable for much more than any side-effects from moving WorkItems.)

The Crux of the Approach

The TFS database is highly complex as it allows for custom fields and future defined workflow to be accomplished. There are several views in the database that support the myriad of joins and filters needed to stitch together a work item from various database tables. At the heart of the work item schema are several base work item tables, with duplicate data, that manage the state transitions and caching of work items. These base tables have no direct reference to the project that the work item is contained in. Project containment is derived from both the Area record as well as the Iteration record which do, each, have an indirect record association to the project.

The query below highlights what is needed to void your TFS Support Contract and move a work item to another project. In short, change both the AreaID and the IterationID to an AreaID and IterationID that belong to the target project. These are existing and normalized values.

UPDATE [Tfs_xxx].[dbo].[WorkItemsAre] _
SET AreaID=@AreaID,IterationID=@IterationID WHERE ID=@WorkItemID
UPDATE [Tfs_xxx].[dbo].[WorkItemsLatest] _
SET AreaID=@AreaID,IterationID=@IterationID WHERE ID=@WorkItemID
UPDATE [Tfs_xxx].[dbo].[WorkItemsWere] _
SET AreaID=@AreaID,IterationID=@IterationID WHERE ID=@WorkItemID

The xxx in Tfs_xxx should be replaced with the correct project collection database for your installation, which can be found from SQL Manager or deduced from the Team Foundation Administrative Console.

Using the TFSMove Utility

I created the TFSMove utility to increase convenience and decrease the human error of hand entered SQL in SQL Manager. Above are links to download the executable files for the utility as well as a slightly simplified version of the source for the utility.

Entering the URL to your TFS server fills the Project ComboBox with the projects to select from.

Example:

https://<server>:<port>/tfs

Enter a SQL Connection String to the SQL Server hosting the TFS database.

Example:

Server=<server domain name>;Database=Tfs_xxx;Trusted_Connection=yes;

You will need to specify and authenticate to the TFS database, e.g., Tfs_xxx, where xxx refers to your TFS Project Collection.

If you do not have Windows Integrated Access working, you can create a database user specifically for this access and provide user id and password credentials in the connection string. Otherwise, you have to specify Trusted_Connection=yes;.

If needed, get help with your connection string and your database access rights from your Database Administrator.

Use the ¡Test! button to ensure that the connectivity is functional. If it works, it will tell you the number of work items in your project collections. Otherwise, it will display the SQL Server connectivity error that caused it to fail.

In the Query: field, you can either enter a work item number (e.g. 17982) or a TFS WorkItemQuery. The WorkItemQuery is a limited SQL language that is used by the TFS web interface for work item queries (see this link). If you do bulk moves, it is helpful to use the [Area Path] condition in the SQL WHERE clause so that the target Area is a better match to the replacement Area of the destination project.

Example:

SELECT * FROM WorkItems WHERE [Area Path]='<ProjectName>\<AreaName>'

The Search button will fill the results pane with a detail of a single work item or an Id and Title list of multiple work items resulting from the Work Item Query.

Select a target Project, target Area, and a target Iteration for the work item(s). Once these and all of the above conditions are met, the ¡Move! button will be enabled. Pressing the ¡Move! button will execute the parametrized form of the SQL referenced above to move the work item(s) to the selected project.

Example:

UPDATE [Tfs_xxx].[dbo].[WorkItemsAre] _
SET AreaID=@AreaID,IterationID=@IterationID WHERE ID=@WorkItemID
UPDATE [Tfs_xxx].[dbo].[WorkItemsLatest] _
SET AreaID=@AreaID,IterationID=@IterationID WHERE ID=@WorkItemID
UPDATE [Tfs_xxx].[dbo].[WorkItemsWere] _
SET AreaID=@AreaID,IterationID=@IterationID WHERE ID=@WorkItemID

History

  • 2017.07.11 – Responded tangentally to comments from Member 13303006 and added introductory credit to prior art.
  • 2017.01.17 – Corrected ‘disappeared graphics’, plus some very minor edits for clarification.
  • 2016.11.18 – Received (presumed) official communique from the Microsoft Program Manager for Visual Studio Team Services (see this link) stating:
    • Microsoft is working on natively enabling the movement of WorkItems between projects
    • This approach will void your TFS Support Contract
    • This approach can cause “serious upgrade issues”
  • 2016.09.30 – Update: Though this functionality has been expressed in the latest Visual Studio Team Services (circa 2016), it has been reported that Microsoft did not follow through on their promise to enable this in TFS 2015 Update 3.
  • 2016.09.16 – Update: TFS 2015 Update 3 now natively allows movement of workitems between projects (see this link)
  • 2015.08.11 – Initial release of source and utility

LEAVE A REPLY