Project Online – SSIS OData Replication – Lessons Learned

I recently got a chance to work on an SSIS Package in Windows Azure environment which periodically retrieved the Project online data via OData connector and stored over an Azure SQL Database for SSRS based reporting.

 

Here are some of my takeaways from this exercise.

 

Learning Resources

The article to get you started is

 MSDN Article: Replicate Project Online Reporting Data to a SQL Server database using the OData protocol

 

if you are also interested in learning about fetching SharePoint List data using similar mechanism, check this blog by Martin Laukkanen

 http://nearbaseline.com/blog/2014/04/project-site-custom-list-reporting-using-ssis-odata-connector/

 

Speed

The SSIS job gets data alright once it is setup. But oData is inherently slow and it can easily take minutes for each project if you have many SharePoint lists or if you are trying to retrieve time phased data sets. Here are some of the suggestions to workaround this limitation.

 

1) Create a separate job for time phased data. And run this job only once in a week or month. Your time phased data will not be nightly refreshed but rest of the data update won’t suffer delays because of it.

2) Download the data in a cache Table. For reporting, use a different copy of same table. Update the copied table once your downloading job is complete. This way, your reports will show last downloaded data when the current downloading is in progress.

3) Check out this demonstration package on Microsoft site. I haven’t tried it but it is designed to sync only changes from Project Online.

Project Online: SSIS package for OData delta sync

 

Logging / Error Handling

SSIS has great logging features. Use them. You can log to text file, SQL Tables, event logs etc. You can also control the the minimum level being logged depending on the log medium and other variables.

 

SSIS also has intensive error handling options available for handling data level error (e.g. Custom field deleted or SharePoint list column deleted from a particular site). Review these options and use according to your requirements

 

Schema

Project Online oData schema is a bit different from traditional Project Server reporting database. The tables are more flattened which are easier to build queries on. However, if you have already built reports for on-premises, keep some time aside to re-write the queries according to new schema.

 

Limitations

There are still some limitations when you retrieve data from oData. e.g. You can’t retrieve more than 45 text fields (and you can’t choose which 45). As of writing these lines, Workflow Phase and Stage for the current project can’t be retrieved  either.

The rapid pace at which Project Online oData support is being enhance by Microsoft, these limitations would hopefully be removed at some point in future.