TFS Work Items as Time Cockpit Tasks

Overview

Microsoft Team Foundation Server and its hosted counterpart Team Foundation Service offer lots of different features for everyday development tasks. Besides version control, continuous integration and test management, work item tracking is an important feature. To make use of the information stored in your TFS work items and to be able to book your times on these items they have to be imported into time cockpit. This article shows how to create a simple IronPython script or time cockpit action that queries TFS work items and stores them as time cockpit tasks.

A similar article is available which shows you how to import issues if you are using JIRA.

The full sample can be found at our github repository.

Changes to the Data Model

For querying the work items a TFS server URI as well as the names of the team projects to import are required. To store this information the following properties have to be added to the APP_Project entity in time cockpit:

• TfsServer (Text, nullable, 1000 characters) to store the URI to the TFS server (e.g. https://timecockpitdemo.visualstudio.com/DefaultCollection)
• TfsProject (Text, nullable, 100 characters) to store the team project name (e.g. Demo, Development)
• TfsLastUpdate (DateTime, nullable) to keep track of the latest known updated work items

TFS API in Python

A TFS server can be accessed via the team foundation client which is shipped with Visual Studio and Team Explorer. You can have a look at the TFS signal tracker prerequisites should you require additional information on DLLs, supported versions and download sources.

The following snippet shows an IronPython helper class for TFS access. The constructor takes the server URI as an argument. The connect method establishes a connection and ensures that authentication succeeded. The method query_work_items takes a project name and last update date as parameters to build and execute a work item query. The result is a collection of work items which is returned by the function.

All the heavy lifting concerning web requests, serialization and authentication is handled by the TFS client.

# Simple TFS API wrapper
class TFS(object):
    def __init__(self, uri):
        from System import Uri
        self.uri = Uri(uri)
        self._connected = False

    def connect(self):
        import clr
        clr.AddReferenceToFileAndPath(r"C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\ReferenceAssemblies\v2.0\Microsoft.TeamFoundation.dll")
        clr.AddReference("Microsoft.TeamFoundation.Client.dll")
        clr.AddReference("Microsoft.TeamFoundation.VersionControl.Client.dll")
        clr.AddReference("Microsoft.TeamFoundation.WorkItemTracking.Client.dll")
        from Microsoft.TeamFoundation.Client import WindowsCredential, TfsClientCredentials, TfsTeamProjectCollection
        tfsCreds = TfsClientCredentials(WindowsCredential(), True)
        self.server = TfsTeamProjectCollection(self.uri, tfsCreds)
        if self.server is None:
            raise InvalidOperationException("Could not get TFS server for " + self.uri + ".")

        self.server.EnsureAuthenticated();

        if not self.server.HasAuthenticated:
            raise InvalidOperationException("TFS could not authenticate.")
    
        self._connected = True

    def query_work_items(self, projectName, fromDate):
        from Microsoft.TeamFoundation.WorkItemTracking.Client import WorkItemStore
        from System.Collections.Generic import Dictionary

        if not self._connected:
            raise InvalidOperationException("TFS not connected.")
        
        workItemStore = self.server.GetService(clr.GetClrType(WorkItemStore))

        if workItemStore is None:
            raise InvalidOperationException("Could not get WorkItemStore.")

        parameters = Dictionary[String, String]()
        parameters.Add("Project", projectName)
        query = "Select [Id], [Title], [Changed Date] From WorkItems Where [System.TeamProject] = @Project"
        if fromDate is not None:
            query = query + " And [Changed Date] >= '" + fromDate.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture) + "'"

        query = query + " Order By [Changed Date] Asc"

        return workItemStore.Query(query, parameters)

Please note that while being a bad practice to use string concatenation or string builders for handling parameters there are situations where it is unavoidable. As seen in line 40 the project name is handled via the parameters collection. If the date is passed as a parameter there can be several issues related to culture info and date formats. If the DateTime is added as a string the API will complain that compared types (String vs. DateTime) are not compatible. If the parameters dictionary is changed to Dictionary[String, Object] the API will implicitly call ToString which will result in an invalid date format in most cases.

Creating and Updating Tasks

Now that TFS connectivity can be easily handled we need to look at which projects need to be updated, query the data and update the corresponding objects in time cockpit. The following sample does so by executing the following steps:

• Get all projects with TFS meta information.
• Group the projects by TFS servers to avoid redundant connection attempts.
• For each TFS server and project the work items changed since the last update are queried.
  • The existing time cockpit tasks corresponding to the TFS work items are selected.
  • The necessary create or update operations are executed. Newly created tasks will be assigned to the correct project.
  • By looking at the latest update timestamp of all changed work items we determine the maximum update date and store it in the time cockpit project.

# get all projects relevant to the TFS import
tfsProjects = dc.Select("From P In Project Where :IsNullOrEmpty(P.TfsServer) = False And :IsNullOrEmpty(P.TfsProject) = False Select P")

# get all the different TFS servers used in the projects
projectsByServer = tfsProjects.GroupBy(lambda p: p.TfsServer)

for serverProjects in projectsByServer:
    tfs = TFS(serverProjects.Key)
    tfs.connect()
    for project in serverProjects:
        dc.BeginTransaction()
        try:
            updatedItems = tfs.query_work_items(project.TfsProject, project.TfsLastUpdate)
            if updatedItems.Count == 0:
                Logger.Write(LogLevel.Verbose, "No updates for project '{0}'.", project.Code)
                continue

            lastUpdate = project.TfsLastUpdate;
            maxUpdate = project.TfsLastUpdate

            for item in updatedItems:
                id = str(item.Id)
                if lastUpdate is None or item.ChangedDate >= lastUpdate:

                    # try to retrieve existing task from time cockpit
                    task = dc.SelectSingleWithParams({ "Query": "From T In Task Where T.Project = @ProjectUuid And T.Code = @Code Select T", "@Code": id, "@ProjectUuid": project.ProjectUuid })
                    if task is None:
                        Logger.Write(LogLevel.Information, "Creating task '{0}' for project '{1}'.", id, project.Code)
                        task = dc.CreateTask()
                        task.Code = id
                        task.Project = project

                    if task.Description != item.Title:
                        Logger.Write(LogLevel.Information, "Updating title of task '{0}' for project '{1}' from '{2}' to '{3}'.", id, project.Code, task.Description, item.Title)
                        task.Description = item.Title
                    
                    dc.SaveObject(task)

                if maxUpdate is None or item.ChangedDate > maxUpdate:
                    maxUpdate = item.ChangedDate

            # update the latest known update timestamp for the project
            if project.TfsLastUpdate != maxUpdate:
                Logger.Write(LogLevel.Information, "Updating project update date for '{0}' from '{1}' to '{2}'.", project.Code, project.TfsLastUpdate, maxUpdate)
                project.TfsLastUpdate = maxUpdate
                dc.SaveObject(project)
            if commit:
                dc.TryCommitTransaction()
            else:
                dc.TryRollbackTransaction()
        except Exception, e:
            dc.TryRollbackTransaction()
            Logger.Write(LogLevel.Error, "Error while handling '{0}': {1}", project.TfsProject, e.message)

Example

We ran this script using a demo project hosted in a Team Foundation Services account. Several work items of different types and with different descriptions were created.

After running our script we can have a look at the Time Cockpit.UI.log file to check if all the items have been created as expected and that the last update date is also changed accordingly.

Creating task '12' for project 'Demo Project'.
Updating title of task '12' for project 'Demo Project' from '' to 'Simple Bug'.
Creating task '13' for project 'Demo Project'.
Updating title of task '13' for project 'Demo Project' from '' to 'My Backlog Item'.
Creating task '14' for project 'Demo Project'.
Updating title of task '14' for project 'Demo Project' from '' to 'Important Feature'.
Updating project update date for 'Demo Project' from '' to '05/22/2013 22:05:45'.

We then changed item 12 "Simple Bug" to have a new title and ran the script again.

The log file shows that only the modified work item has been processed by the script.

Updating title of task '12' for project 'Demo Project' from 'Simple Bug' to 'Simple Bug (added more info)'.
Updating project update date for 'Demo Project' from '05/22/2013 22:05:45' to '05/23/2013 14:43:26'.

Looking at the list of tasks in time cockpit we see that our project now has the latest version of all the work items and we are ready to assign our time bookings to these tasks.

Wrap-Up

This example shows a small and simple import from TFS. It can be extended to include more information in the import or to push back information to TFS (e.g. actual efforts calculated by aggregating the time bookings per task). We are also evaluating to add this functionality as an out of the box feature for time cockpit and would love to hear your feedback.