Solved! Integrating Bugzilla with MS project 2007

I have been battling for weeks trying to integrate Bugzilla items with Microsoft Project, and finally I’ve got something working.  Truth be said, I had it working a long time ago, but suddenly it stopped working – so it somehow got broken.  I couldn’t figure out what had changed and it was driving me crazy – even my colleagues around me were starting to feel the pain and could see my hair getting pulled out!

Let’s start at the beginning, and explain what I needed and how I got it done:
We use Bugzilla as a bug tracking tool and with each release we need to figure out which features and fixes will make it into the next release.  While Bugzilla is a great fault tracking tool, it doesn’t have the ability (AFAIK) to show when all targeted bugs will be fixed.  In other words, it shows what needs to be done, but doesn’t show when they will be fixed by the assigned developers.

 
We use Microsoft Project to show load on resources (levelling) and for release planning ie: which features and bug fixes will be put into the next release.  So, it’s obvious that importing Bugzilla’s bugs into the project plan will allow us to see what can be done, by who, and by when.

Entering the constantly changing list of bugs into MS Project manually is possible, but extremely tedious, so it becomes an impossible task.  I am still looking for an automated way of doing this… but, we’ve found the next best solution:

Importing Bugzilla into MS Project:
Instead of doing the manual update thing… the next best solution is to import the list of bugs into the project plan as follows:
*) In Bugzilla: change the bugs that need to be fixed in the next release to have a common TargetMilestone.
*) In each bug, update the “Hours Left” field to the estimated number of hours that the fix will take to resolve.  Note: the “Hours Left” field is only visible if the user is a member of the Bugzilla “timetrackinggroup”.  Add peoples’ logins to the group using the “Edit groups” page: eg: @yourcompany.com$|@gmail.com$*) create a view that lists all bugs that need to be imported into the plan ie: all bugs with a TargetMilestone set to “EndJulyRelease” for component “Widget” that have been modified since 2010-06-01 (the last time the bug import was done).
*) Add columns to the results screen (use the “Change columns” link) to add columns that you want to import into the project plan.
*) Export the resulting list by clicking on the “CSV” link (at the bottom of Bugzilla’s results page).  Save the .csv file to your computer – this is the file that will be imported (actually, merged) into the project plan.

*) In MS Project: open your .mpp file, click File->Open, select the .csv file that was exported from Bugzilla; the “Import Wizard” will open.  Click “Next”, then “New map” and define how the fields (in the .csv file) map to the MS project fields as follows:  On the following screens select the following:  “Merge the data into the active project”, then “Tasks”, tick “Import includes headers”, then (in the “Task mapping” screen): Click on the pull down arrows and map each field in the text file to each of the MS project fields.  The key to this import (and subsequent imports that update already imported bugs) is the “MERGE KEY”.  Make the bug_id field the merge key by clicking on the “Set merge key” button.  This will ensure that the next imports of the updated .csv file will not insert duplicate tasks, but rather update the project tasks as identified by the Bugzilla number.
*) Ensure that the “remaining_time” field from the .csv file is mapped to MS project’s “Duration” field, and “short_desc” to “Name”.
*) Save the map so that subsequent imports will be faster since the saved map can be used to import the data.  Click “Finish” and the data from the .csv file will be imported into MS project.  Any existing tasks with a Bugzilla number in the “Text1” field will be updated, while new tasks will be add to the end of the MS project file.
*) Add columns “Text 1” thru to “Text 14” to your Gant chart, and by showing the Bugzilla priority field next (eg: Text5) to the MS Project’s “Priority” field one can see which tasks should have a high MS Project priority that can be used for levelling.

OK, so the above description shows the mechanics for integrating Bugzilla with MS Project, but what got broken?  What changed that was driving me crazy and making me pull my hair out?  The merging the .csv file stopped working – it just imported the Bugzilla tasks over the other project tasks – ie: somehow it was ignoring the merge key! 
I managed to isolate the problem by creating a new, empty project, then pasting the original MS Project’s tasks into the new Gant chart, then the merge of the csv file worked!
This led me to the cause of the problem: I found that by deleting some of the columns in the Gant chart fixed the problem and the merge worked again…

So, if your import of .csv file stops working all of a sudden, then you can try the two solutions above (create a new .mpp file or delete some unused columns).

Update on 16Jan2011: I have now upgraded my MS Project to MS Project 2010 (version 14.0.4751.1000) and the merge from the Bugzilla CSV file imports fine and there are no problems – so no need for me to use a new, empty project :)  Hoooray!


Update 23 March 2011: If you prefer to import Bugzilla bugs into a spreadsheet directly without having to do the following:
1) go to the Bugzilla web page,
2) open (or create) the view of the bugs you are interested in,
3) export the file to a .csv file or copy and paste it into an Excel spreadsheet.
In other words, if you don’t want to do the above each and every time you want an update, then this can be automated by using some Visual Basic for Applications (VBA) script in Excel that will download the content of a view via its CSV file, and display its content in Excel (almost like an API).  Omar BELKHODJA explains what to do – thanks for writing the blog post and for sharing!: http://blog.sos-company.com/?p=3