none
Resource ECF update via transform RRS feed

  • Question

  • Not sure if this is possible, or recommended (which is why I am posting). 

    I want to be able to update our PWA resource fields (and associated ECFs) with a SQL transform from another SQL database from our HRI system.

    I have found a PWA MSP_EpmResource_UserView that seems to have exactly the fields I am looking for. So it seems to me it would be very possible that I can update the resource fields/ECFs from data from another datasource.

    So my questions are:
    1) is there any reason that I should not be doing this?
    2) Is there a better/recommended way to do this?
    3) is it possible to do this with an Active Directory user/group sync (assuming that  this information is in AD custom fields).

    Any thoguhts, comments, suggestions or otherwise would be more than welcome!

    Thank you! 


    Wednesday, November 20, 2013 9:15 PM

Answers

All replies

  • Hello,

    Updating / syncing Project Server custom fields is possible but you wouldn't do this via SQL directly. You would need to do this via the Project Server PSI. You could build an application that took data from the HRI database, transformed the data then use the PSI to update the Project Server data (custom fields, resources etc.) We have done this many times for our clients. If you are not familiar with the PSI, see the links below, I am not sure what version you have so I have included both 2010 and 2013:

    For 2013:

    SDK - http://msdn.microsoft.com/en-us/library/ms512767.aspx

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

    For 2010:

    SDK - http://msdn.microsoft.com/en-us/library/ms512767(v=office.14).aspx

    PSI - http://msdn.microsoft.com/en-us/library/ms488627(v=office.14).aspx

    The SDKs are always a good place to start.

    I'm not sure on your requirements / needs etc. but the other option if you didn't want to sync the data to Project Server via the PSI is to create a report that brings the data together for viewing purposes outside of PWA.

    Paul 


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

    Thursday, November 21, 2013 10:01 AM
    Moderator
  • Paul, thank you very much and sorry for the delay in my response.

    I suppose I was hoping that we would not have to go the SDK/PSI route as we are much more familiar and comfortable with updating the database via SQL and do not have the programming expertise in house. 

    I suppose there may be another route to accomplish this and that would be via the LDAP sync? the catch here is that only the default fields are synchronized and the use of another tool would be required.

    Does anyone know if this codeplex project (or for that matter any readily available 3rd party application) is available that would address synchronizing addition Active Directory fields and/or fields from another datasource such as SQL? http://ps2007adressync.codeplex.com/

    Again we have several ECF fields in '13 Project server that we wish to update/sync with our HRMS system on a nightly (or frequent) basis.

    Thanks again!

    Tuesday, December 3, 2013 5:58 PM
  • What is the purpose of syncing the data? Does it need to be displayed in PWA / Project / used for filtering etc.? If not and it is only for reporting purposes you could just create a report to display the data from Project Server and the external database. SSRS would be ideal for this. Just a thought :)

    Paul


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

    Tuesday, December 3, 2013 6:59 PM
    Moderator
  • Yes, it is to be used for display in the PWA and used for filtering etc. So while I think that your SSRS solution is a good one, its not going to work in this case.

    This is why (ideally) we want to be able to sync/import the source data into the PS ECF fields.

    If anyone has a simplistic method of doing this (IE not using the PSI) then please let me know. It appears that the codeplex projects hit a wall a couple of versions ago.

    Thanks! 

    Tuesday, December 3, 2013 7:33 PM
  • It is unlikely you will get something off the shelf for this - I don't know off any 3rd part tools to do this as each deployment has different requirements. I think you will need to code this yourselves or engage with a project server partner to assist you in the development.

    I know FluentPro have a tool that syncs lookup tables with a SQL database but this isn't what you are after.

    http://www.fluentpro.com/productslookupmanager.html

    Paul


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

    Tuesday, December 3, 2013 8:00 PM
    Moderator
  • I have to agree, not finding much here.

    So let me turn this around, I have the ability to take this (SQL) information and shove it into AD fields. So this would then enable AD to be the "source" for this data.

    That codeplex product worked for 2007, but I am having a hard time finding anything that will work for 2013 off the shelf? does such an AD to Resource sync exist (off the shelf) for PS '13 yet?

    Tuesday, December 3, 2013 9:05 PM
  • There isn't an extended AD sync off the shelf for 2013 like the codeplex solution for 2007. One wasn't even created for 2010, although with minor updates the 2007 one worked for 2010. I don't think that will be the case for 2013. Paul

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

    Tuesday, December 3, 2013 10:51 PM
    Moderator