none
Replicate Project Online Reporting Data to on-premise SQL Server database RRS feed

  • Question

  • Hi Experts,

    As part of reporting enhancements , we are in a plan to Integrate data in local sql server from multiple systems. 

    Now the question is , when I replicate the Project Online Reporting db to Local db using SSIS, will I get the "Project Site" data as well? as we will have most of the data stored under each Project site. 

    If not, Could you please tell me the best way to replicate Project online data along with the Project site data to the Local SQL db server? 

    Please help me, it's an urgent requirement.

    Thanks in Advance.

    Regards,

    Rajkumar.

    Tuesday, August 29, 2017 10:45 AM

Answers

All replies

  • Hi Experts,

    As part of reporting enhancements , we are in a plan to Integrate data in local sql server from multiple systems. 

    Now the question is , when I replicate the Project Online Reporting db to Local db using SSIS, will I get the "Project Site" data as well? as we will have most of the data stored under each Project site. 

    If not, Could you please tell me the best way to replicate Project online data along with the Project site data to the Local SQL db server? 

    Please help me, it's an urgent requirement.

    Thanks in Advance.

    Regards,

    Rajkumar.

    Tuesday, August 29, 2017 1:26 PM
  • Hi Rajkumar, SSIS is using the ProjectData feed which is based on Odata. Risks and Issues from the Project Site are supported, but any additional list or field needs customization. Check out this guidance, how to setup the ssis connection: https://msdn.microsoft.com/en-us/library/office/dn720853.aspx Also fluentpro has a product called EPM Pulse Datamart, maybe that's also an option for you.

    Best regards,

    Julian, PMP, MCP, MCSA, MCSE

    Blog: https://project-brain.com

    Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

    Tuesday, August 29, 2017 6:57 PM
  • Hello Rajkumar,

    You can do it with SSIS and get the custom columns from the Project Sites - as well as custom lists too. For a SSIS example see this blog post from Martin: https://nearbaseline.com/2014/04/project-site-custom-list-reporting-using-ssis-odata-connector/

    We (CPS) also have a solution that requires no code / no servers etc. and copies the data to an Azure SQL Database: http://projectserverplus.com/app/ps-datastore/ It is part of a wider solution hence not much information on the site but this is also available on its own. It provides a UI to set what you want to sync (Project data and and SharePoint list data from the Project Sites). *Disclaimer - I work for CPS*. There are other 3rd party tools out there that do similar functions too if you didn't want to develop something yourself.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Tuesday, August 29, 2017 7:20 PM
    Moderator
  • I have done it using SSIS, I was able to read eveything including custom lists etc..

    Thank you.


    From, Microsoft Tech forum.

    Wednesday, January 31, 2018 3:30 AM
  • Hi Paul, Glad to know about this solution that you provided...

    Kindly let us know if it is also possible to connect to Project Online from Sql Server installed on AWS EC2...

    Thanks in advance

    Tuesday, January 15, 2019 5:11 PM
  • Hello,

    It depends what solution you you are using - but technically there is no reason why the SQL DB couldnt be hosted in AWS. The applicaction / process you build will just need access to the SQL DB where ever it is hosted.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Tuesday, January 15, 2019 5:17 PM
    Moderator