How to Get Data from Microsoft Project into Your BI Tool

Project management is important to any release. There are several quality tools out there to assist with project management and workflow, but a lot of organizations use Microsoft Project due to its legacy and the depth of its features.  What if you want to get your data out of Project Online and into your existing BI environment for integrated reporting on things like costs, risks and issues, and completion status?

Microsoft has tight integrations throughout their technology stack, so the easiest approach would be through Power BI or Excel. Your organization may already have a BI solution in place, so reverting back to decentralized Excel-based reporting is not ideal.  Additionally, you may not have the capacity, budget, or desire to introduce Power BI as a replacement for your existing solution.

So how can you bring data from Project Online into your existing reporting environment such as Qlik or Tableau?  In this blog post I will show you a way to do that, along with a pesky bug that may accompany your implementation, and how to work through it.

Setup

For the setup we will use a fictional company, KTS LLC, with SharePoint and Project Online site collections located at https://ktsllc.sharepoint.com/.  In Project Online I’ve created a project, ‘testproject’, and made some tasks:

Make sure that the user (login) that you will use to connect will need the appropriate permissions to access the OData feed as outlined in the service reference.

Connect to Project Online using OData Source

On the surface, connecting to Project Online via SSIS is straightforward: Simply create an OData connection manager combined with an OData Source and then write to a database or flat file.  However, this seemingly easy solution can have its share of problems, as experienced by colleagues and me while working on a project for one of our clients.

The Bug

Open up SQL Server Data Tools and create a new OData connection:

Enter in the credentials and service document location.  For Project Online the service document location format is https://yoursitecollection.sharepoint.com/sites/pwa/_api/ProjectData .  Test the connection, and it succeeds:

Then create an OData Source within a data flow task using the same connection manager:

The collection list is visible in the dropdown menu:

However, when clicking ‘Preview’ or attempting to view the column list, Visual Studio locks up completely and you see the following message:

For me, the problem was very repeatable, and frustrating.  Even after waiting an hour, VS would not recover, and I would have to manually kill the task in the task manager and receive the prompt that VS is not responding.

Thankfully, after some time on the phone with a very helpful (and patient) Microsoft SQL Server Support engineer, it was suggested that I try the fix outlined here.

Writing to a Flat File from OData Source

After the fix, fire up SSDT, open up the OData Source, pick your collection (we will stick with the tasks example), and click ‘Preview’:

Success!  Now we can choose our columns:

Finish up the data flow task with a flat file destination:

Execute the data flow task within the control flow, and check the output:

In this simple example we wrote the output to a flat file, but we could have used a database destination instead; choose the preferred method that would most easily integrate with your existing BI environment.

Conclusion

With assistance from Microsoft tech support, we were able to get our client’s data out of Project Online and into Qlik for enhanced, centralized project management reporting.  It is no secret among IT professionals that, when encountering bumps in the road with an implementation, we rely on the help of strangers on the internet through Stack Overflow, product forums, or individual blog posts.  If you found this post useful, I would highly encourage you to ‘pay it forward’ and share your fix online… you never know who it may help!