Thanks Marc Boyer and Diane Diaz for the new document and demonstration package that shows how to do incremental downloads of assignment time phased data from Project Online Project Web Apps into a SQL Server database table. This makes use of the SQL Server Integration Services (SSIS) OData feed that was introduced earlier in the year for SQL Server 2012, and is included in SQL Server 2014. The demonstration uses a local SQL Server as the destination – but you could modify to use SQL Azure or any other OLE DB destination.
I downloaded the package and had a quick play about just in case extra questions come via the blog that I might need to answer. The download can be found at http://www.microsoft.com/en-us/download/details.aspx?id=43736 and contains two files, a PDF with instructions and a zip file of the SSIS package itself. If you happened upon this last week you might not have seen the PDF – so worth going back and downloading.
Of course I didn’t read the pre-requisite piece and thought I already had them all (I didn’t) – so you will need at least the shell of Visual Studio 2012 and the Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2012 - http://www.microsoft.com/en-us/download/details.aspx?id=36843 or the 2013 version is out there too - Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2013 . If you don’t have these then you will not see the Business Intelligence section when you click on New Project in VS:
Once you have that part working you can follow the PDF to add the sample package using the right click menu on SSIS Packages:
If you haven’t loaded the Microsoft® OData Source for Microsoft SQL Server® 2012 then you will get an error like this, saying Error at Odata AssignmentsTimephased (): The connection type “ODATA” specified for connection manager “OData Assignments Feed is not recognized…
So head off to http://www.microsoft.com/en-us/download/details.aspx?id=42280 and download it!
Once you have that then you are good to go! Set the parameters in the package (I decided I wouldn’t show you my password…) and you can start pulling data from your Project Online Project Web App. It worked as advertised – don’t forget if you add new assignment information it will not get downloaded until the project is published and the package is executed again – but as noted in the PDF – I found the subsequent data loads after making a few project updates and adding new projects to be pretty quick (YMWV - my demo dataset isn’t huge).
With the default settings you will get a new database called ODATA with a couple of stored procedures and tables. The main table called AssignmentsTimephased that will be the target for the downloaded data – and another table called Synced, which keeps track of the date and time the last sync happened.
I hope this will help a lot of our Project Online customers get started with OData in an efficient and useful way.