# Wednesday, 27 January 2016

RippleRock are developing an awesome tool for providing realtime, actionable visualisations of your project data.  If you’re wondering why your backlog is such a mess, want to understand how you could improve your Agile/Lean development process or simply want to predict when your project will end, then you should check out SenseAdapt.

SenseAdapt works with Team Foundation Server and Visual Studio Team Services (keep an eye out for a preview on the new VSTS Marketplace soon) and we’re experimenting with a Jira version too.

To develop SenseAdapt, we use our own project data, so SenseAdapt is helping us to improve and adapt the process we use to develop SenseAdapt!

The problem is that we need real data in both VSTS and TFS and as we recently consolidated a VSTS project into our on-premise TFS 2015, we wanted a one-way sync of Work Items between the two systems.

There are options of course:

I wanted something implemented quickly and cheaply so that’s why yesterday I installed the TFS Integration Tools.  If you have ever used TIT (they really should have thought about that acronym) then you’ll know that they are……er, interesting?

Last released in March 2012, they know nothing of TFS 2013/2015 and VSO/VSTS was the TFS Service Preview back then.  My installation was on Windows Server 2012 R2 with SQL Server 2014 Standard SP1 and TFS 2015 RTM (No update 1 on this instance for testing purposes)

I knew that I needed to install the TFS 2012 Team Explorer so that the Integration Tools could talk to my server using the older object model.

I also knew there was a problem with this setup that needed a little registry change.  The error you will see is:

“This tool requires the TFS client object model.  Please install Team Explorer 2008, Team Explorer 2010, or Team Explorer Dev11 and run setup again”

An old blog post by Willy Peter Schaub shows that the easiest way to fix this is to paste the following into a .txt file, rename to .reg, and double click.

Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\VisualStudio\11.0\InstalledProducts\Team System Tools for Developers]
@="#101"
"LogoID"="#100"
"Package"="{97d9322b-672f-42ab-b3cb-ca27aaedf09d}"
 "ProductDetails"="#102"
"UseVsProductID"=dword:00000001

I was off and running now so I kicked off the install that I’ve done many times in the past and selected to include the TFS Integration Service

image

For the database server name, i just put a ‘.’ as I was going to use the default instance (SQL 2014 Standard)

image

Unsurprisingly, something went wrong but I aborted the install without really paying attention, assuming I messed something up.

So I started the installation again, paying more attention this time but I received a different error:

image

Database Error: Cannot read TFS Integration Tools database version number

It turns out that the failed installation had created the database Tfs_IntegrationPlatform but had not cleaned it up again during rollback.  That helpful message meant that I needed to go into SQL Management Studio and delete the database.

We were off again and everything seemed good until the error message that I received originally showed up again.

image

Error -2147217900: failed to execute SQL string, error detail: Valid values of the database compatibility level are 100, 110, or 120., SQL key: CreateDatabaseScript SQL string: EXECUTE sp_dbcmptlevel [Tfs_IntegrationPlatform], 90;

This one was trickier.  There was an ooooold forum post by Bill Essary at Microsoft saying that they were aware of the issue but didn’t have a fix as yet.  A few others suggested that the problem went away when they retried the install.  After the 3rd time, I accepted it wasn’t going away.

Making an educated guess, i assumed that the integration tools were not compatible with SQL 2014 so I decided to install a named instance of SQL 2012 Standard SP3 on the same server.

Passing in .\INTEGRATION as the database server\instance meant we were finally installed!

Onto the next problem

image

I created a new configuration and selected Team Foundation Server\WorkItemTracking.xml as my template.

image

I didn’t bother reading the docs as I was still sceptical that this was going to work so I just chose Custom workflow, ContinuousAutomatic frequency, Unidirectional direction.

image

Next, I chose my on-premise TFS 2015 server as the Left Source and my VSTS account as the Right Source.

image

I had forgotten that the account running the Sync/Migration had to be in the Service Accounts Group.  On-premise you have to use tfssecurity.exe to add your account to that group but I suddenly thought, “there’s no way VSTS will let me do that”, so I expected it to be game over.

image

I tried:

tfssecurity.exe /g+ “Team Foundation Service Accounts” n:my@emailaccount.com /server:https://myaccount.visualstudio.com

and unsurprisingly got

The requested operation is not allowed

image

Kind of obvious as VSTS is not going to allow me to be a Team Foundation Admin or Service account so I had one last stab:

tfssecurity.exe /g+ “Project Collection Service Accounts” n:my@emailaccount.com /collection:https://myaccount.visualstudio.com/defaultcollection

image

Amazingly, that was successful

image

I repeated the process for my on-premise server and I was off.

Now, the tools are installed and running but as usual, it has thrown a hissy fit on the first pass.

image 

Application Error: 0 : A failure occurred while trying to load the C:\Program Files (x86)\Microsoft Team Foundation Server Integration Tools\Plugins\Tfs2008WITAdapter.dll Plugin:
System.Reflection.ReflectionTypeLoadException: Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.

and

Application Error: 0 : A failure occurred while trying to load the C:\Program Files (x86)\Microsoft Team Foundation Server Integration Tools\Plugins\Tfs2010WITAdapter.dll Plugin:
System.Reflection.ReflectionTypeLoadException: Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.

and

Application Critical: 0 : [27/01/2016 19:07:38] System.InvalidOperationException: Specified element is already the logical child of another element. Disconnect it first.

After all that, those errors looked like they could be terminal but I found an article that states that removing any files with 2008 or 2010 in the name from the following folder:

C:\Program Files (x86)\Microsoft Team Foundation Server Integration Tools\Plugins

will allow the Integration Tools to progress.

Great source for all things Integration Platform: TOC: TFS Integration Tools Blog Posts and Reference Sites

And we’re off and running with 1-way sync between TFS 2015 and Visual Studio Team Services!

image

Cheers,

Richard



.

Wednesday, 27 January 2016 19:31:03 (GMT Standard Time, UTC+00:00)  #    Comments [0]


# Tuesday, 22 December 2015

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)

If you want to migrate source code history then you’re looking at something like the TFS Integration Tools for on premise TFS or the OpsHub Visual Studio Online Migration Utility for VSTS.  They will also migrate Work Items and their links. 

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. 

image

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.

image

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.

image

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

image

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.

image

Click on Edit Query

image

And Select File-> Save As…

image

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. 

image

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. 

image

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.

image

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

image

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.

image

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

image

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.

image

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.

image

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.

image

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.

image

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.

image

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.

image

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

image

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.

Colin Dembovsky has a great blog post - Bulk Migrate Work Item Comments, Links and Attachments – which provides a PowerShell script which is really helpful. 

I want to talk about some of the other issues such as Test Steps and Area Paths in another article but for now it’s time for Christmas.

Cheers,

Richard



.

Tuesday, 22 December 2015 18:16:26 (GMT Standard Time, UTC+00:00)  #    Comments [0]


# Wednesday, 09 December 2015

Writing queries in TFS is really simple, right?

  • Flat query showing high severity Bugs with low effort estimation in the modelling area?

“No problem.”

  • Direct Link query showing only Backlog Items that do not have an associated Test Case in a Ready state? 

“Here you go…”

  • Tree query showing a hierarchy of Epics with a high business value with Features, PBIs and Bugs that are in Release 2? 

“Errr, let me think about that!”

That’s why with TFS 2013 I always found it easiest to open up my Backlog, change the view to show a tree, for example Features to Tasks…

image

Then I could hit that little Create Query button…

image

name my query…

image

And Bob’s your uncle, I have a starting point Tree Query generated for me that I can mess around with to get what I need.

image

I did this as normal on Visual Studio Team Services (formerly Visual Studio Online or VSO) the other day and to my horror, my generated query didn’t show the hierarchy I was looking at in my backlog view meaning that I actually had to think about it for the first time in a while!

In VSTS/TFS 2015, the backlog view has changed a little and now the Parent/Child view is either On or Off.

image

The create query button is still there and it generates a tree query but it only shows Product Backlog Items or Bugs with any nested items the have (which I don’t encourage you to do, keep that backlog flat as you can’t reprioritise nested items independently).

So I had to generate my own starting query.

In this instance I just wanted EVERYTHING, but in a hierarchical view so I started here:

image

What this tree query is doing is to match the top level clause first (Any work item) and then match any child items which meet the criteria (Any work item) which in this case showed Epics – Tasks, ignoring any state or other criteria such as Area or Iteration Paths.

image

So what changed?

If I look at the generated query from TFS 2013 we can see what it is doing (and understand why TFS 2015 doesn’t work that way anymore)

image

Remember this query was looking at Features and then any linked Features, Bugs, PBIs or Tasks.  Any PBI not rolling up to a Feature would not appear in the results.  Also, importantly the query is sorted by Backlog Priority followed by ID.

image

If we look at an alternative view which is Backlog Items to Features, what it will show is my entire backlog of PBIs/Bugs and any parent Features.  Any Features that do not have child Requirements will not show up in the results.

image

The difference here is that we are comparing the linked Work Items first (any PBIs/Bugs meeting the criteria) and then looking for any parent items that they have that are either more requirements or Features.

How do I reproduce it in VSTS?

In TFS 2015/VSTS the generated query I initially expected is probably closer to my Any Item linked to Any Item query, just with a few restrictions.  For instance my top level query might look something like this if I want to see Features, Bugs, PBIs and Tasks and any Children they have.

image

Think carefully about what you want to see at the top level.  Maybe you only want to see Features (or Epics or whatever) and the tree below them – and then have a separate query for any orphaned PBIs, Bug or Tasks.  There are lots of edge cases such as a PBI in an active state but its parent is closed so make sure you’re not losing things.

Good queries are really only useful against a well organized backlog.  If you need help to organise your backlog and really understand how your project is doing then get in touch with RippleRock.

Cheers,

Richard



.

Wednesday, 09 December 2015 19:37:40 (GMT Standard Time, UTC+00:00)  #    Comments [0]