none
Load data from SQL Server into Porject Server 2013 RRS feed

  • Question

  • hi,

    To begin a short presentation :

    I'm Alexander (22y old) and i work for an SSII (in internship). I'm also an MSP. 

    So, i had to create a Sharepoint with Project Server.

    The Project Server has to display the SQL information

    Simple Exemple : 

    i have the name_task, id_task, start_date, end_date etc.. row in sql 

    how can i lead Project Server to take this information in the MSSQL DB and display them in a traditional planning ?

    Thx a lot. 

    Monday, June 24, 2013 12:44 PM

All replies

  • Hello,

    If you want to import data into Project Server from a SQL database you will need to look at using the APIs:

    PSI: http://msdn.microsoft.com/en-us/library/office/ms488627.aspx

    CSOM: http://msdn.microsoft.com/en-us/library/office/jj163123.aspx

    Download the SDK for examples of using the PSI and CSOM: http://www.microsoft.com/en-gb/download/details.aspx?id=30435

    Thanks

    Paul


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

    Monday, June 24, 2013 12:48 PM
    Moderator
  • Thanks, i've downloaded the SDK

    i'd never used PSI or CSOM before (barely heard of it)

    But there's always a 1st time :)

    Monday, June 24, 2013 1:15 PM
  • I would use Excel, and paste the results into Project Pro.
    Monday, June 24, 2013 7:59 PM
  • What do you mean by using Excel and paste it ?

    I've started to learn PSI and CSOM (but i don't really get it) 

    i found this : http://msdn.microsoft.com/en-us/library/office/ee767688(v=office.14).aspx 

    but in this exemple like the others, the topic demonstrate how to connect your Project Server to your RDB.

    For my case i want to connect Project Server to my MSSQL Server (i got my prods table here) in purpose to display plannings with my SQL Data.

    To resume :

    I got MSSQL Server 2012

    Project Server 2013

    In MSSQL Server i got 3 Tables : _temp, Ressources_table, prod_table. My Project Server has to Display plannings with those data.

    In PSI i found the attribute : lookuptable

    do i get closer ?, if no wich attribute sould i use ?


    • Edited by it-Alexander Tuesday, June 25, 2013 2:41 PM missed url
    Tuesday, June 25, 2013 2:22 PM
  • Hello,

    The excel route is possible for bulk one off imports. The PSI / CSOM route would be better as you could extend this further and create a timer job to run this code on a nightly / weekly basis to automcatically update / create new projects in Project Server from your custom database. Is this is something you need or is it a one off? It might be best to engauge with a Project Server developer to help you - strongly recommended if you have not got previous experience of the PSI / CSOM.

    Paul


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

    Tuesday, June 25, 2013 2:41 PM
    Moderator
  • I would use sql command get the data out to excel the design a view in Project that would match the layout of the Excel and Copy paste. prod_table may have the tasks and Resources_table resources.
    Each task in Project has a hundreds of fields and relationships. Pasting task name and resource into project Pro keeps all other things natively generated. But I agree with PWMather that if you were needing to do this on an ongoing basis It maybe worth coding. If it was for one time 100 projects, I'd copy and paste. I have a hunch though that your source system has more going on (relationships) than just 3 tables. You will need to translate those to project as well so that might be where the PSI can help also.
    Tuesday, June 25, 2013 8:55 PM
  • What....nobody's mentioned VBA?  I find these sorts of things are perfect for VBA.  Open the project in the client.  Run the VBA to update from a database.  Save and Publish.

    This gives you much more control over the schedule and the various scheduling options.


    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky

    Wednesday, June 26, 2013 2:39 AM
    Moderator
  • Good catch Andrew :)

    Again, VBA would be ok for the manual / one off type syncs I guess - for your data to be in sync with the external source the project would need to be open in Project Pro, run the VBA then save and publish.

    It all depends on the requirements, there are many options to solve many problems :)

    Thanks

    Paul


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

    Wednesday, June 26, 2013 8:23 AM
    Moderator