Remove mapping between TFS and Project Server with empty Project (deleted Gantt)

As TFS admin I was asking to synchronize TFS with Project Server. There is nothing really special and MSDN is full of articles and guides about it.

But, what MSDN doesn’t say you is that if you had mapped a project on EPM with a team project on TFS and then you have deleted the project from EPM, you would experienced a lot of pains! You are not able to edit the synchronization system and, if you run the “TfsAdmin ProjectServer /GetMappedProjects” command, it will return you something like “Enterprise project  is mapped to team project XXX” where XXX is not an issue (it is the team project name) instead the big issue is the blank space between words “project” and “is”!!!

So, first piece of advice: never delete a project from Project Server without having unmapped it from TFS before.

But, what if someone else has deleted it without having unmapped it? Well, Microsoft doesn’t give you any solution and googling for it I didn’t find anything. Anyway, after having spent several months on it, I have found a solution.

But please attention: it is not a recommended solution by Microsoft: it is a my personal solution and it worked for me; anyway it could be really dangerous, so use it carefully and only if you are absolutely confident you are able to do it.

1. Connet to the TFS Collection database (the name should be something like “Tfs_<CollectionName>”)
2. Query table named “tbl_sync_mappings”: it should contain only one record
3. You need to extract field named “mapping”: it contains an xml string; copy it in a text editor
4. If you analyze the xml, you could find several useful infos but what you need is the “tpcProject” set of items: it contains on “psProject” item for each mapped EPM project!
5. Compare this set with the output of the “TfsAdmin ProjectServer /GetMappedProjects” command and you will discover the deleted EPM projects. (If you want, you can query the MSP_Projects table inside ProjectServer_Published database with the id field of the “psProject” element to be sure that you are going to cut off the right one)
6. So, what you need to do is simply eliminate the “psProject” element of “ghost” project and update the “tbl_sync_mappings” table: pay attention to not put any strange characters or space inside the xml!
7. Et voilà, that’s all! Now run the “TfsAdmin ProjectServer /GetMappedProjects” command again and you will not see your deleted project anymore…