Read Project Server 2010 CustomFields over PSI

 

I’m developing software for the MS Project Server 2010 for just a few days. But still after these few days I realized that Microsoft offers a very bad documentation for the PSI and the whole Project Server stuff.

So i decided to share my knowledge within this blog!

One of the major need for Project Server administrators is to synchronize data, for example  from  SAP, into custom fields of a specified project. So I’m going to show how to…

  • …read data from projects
  • …read data from custom fields
  • …lookup a choice custom field

and in another blog entry how to…

  • …write project data
  • …write values into custom fields

with C# .NET 4.0!

 Setup Web service and Project

At the beginning we have to  setup the soap connections we well need for this tutorial. All Web services are documented here: Project Server 2010 Class Library and Web Service Reference

We’re going to use these three services:

The URL to the specified service looks always the same:

http://servername:port/sitename/_vti_bin/PSI/servicename.asmx?wsdl

So we’re going to add all these three Web services to our service references of the project:

 

Authentication with special credentials

If we now would try to connect to the server, we will  certainly run into an error because of an authentication failure. To prevent us from this error we have to modify the security setting of the binding (just do this if you run into an authentication error during the connection):

 <!--security binding for windows authentication--> <security mode="TransportCredentialOnly"> <transport clientCredentialType="Ntlm" proxyCredentialType="Ntlm" realm="" /> <message clientCredentialType="UserName" algorithmSuite="Default" /> </security> <!--default configuration--> <!--<security mode="None"> <transport clientCredentialType="None" proxyCredentialType="None" realm="" /> <message clientCredentialType="UserName" algorithmSuite="Default" /> </security>--> </binding>

If you changed your binding, you also have to insert some code to impersonate the authentication:

//credential impersonation (just if you changed your binding settings) projectSvc.ClientCredentials.Windows.ClientCredential = new NetworkCredential("username", "password", "domain"); projectSvc.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation; customfieldSvc.ClientCredentials.Windows.ClientCredential = new NetworkCredential("username", "password", "domain"); customfieldSvc.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation; loockuptableSvc.ClientCredentials.Windows.ClientCredential = new NetworkCredential("username", "password", "domain"); loockuptableSvc.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;
Alternatives

Instead of adding the Web services over the reference dialog, you can get the .NET DLL to interact with the Project Server from the Project Server itself. The two libraries are located in the BIN folder of the Project Server:

C:Program FilesMicrosoft Office Servers14.0Bin

You have to add these two libraries to your project:

  • Microsoft.Office.Project.Server.Library.dll
  • Microsoft.Office.Project.Server.Events.Receivers.dll

Maybe I’m gonna write a tutorial for this later.

Connecting to the Project Server

Now we are able to try to establish a connection. For this we have to initialize the Soap client objects and run our first command to read the list of projects:

//initialize web services ProjectSoapClient projectSvc = new ProjectSoapClient(); CustomFieldsSoapClient customfieldSvc = new CustomFieldsSoapClient(); LookupTableSoapClient loockuptableSvc = new LookupTableSoapClient(); ProjectDataSet projectList = projectSvc.ReadProjectList();

You see that we don’t get a real list of projects. Instead of, we receive a modified data set which contains all informations we need plus some special functions to read them simplified.

If you are unsure what data you need, or how to get them, you can visualize the DataSet with the Visualizer of Visual Studio:

Reading Custom Fields

After establishing the connection, we are now able to read custom field values of a specified field. We have to get the project informations for this:

//read project information Guid myProjectUid = projectList.Project[0].PROJ_UID; ProjectDataSet myProject = projectSvc.ReadProject(myProjectUid, DataStoreEnum.WorkingStore);

I use the first project in the project list for this. We can just read the Guid and get the project informations for the specified project.

It is maybe a little bit strange to work again with the same DataSet like before, but this is Project Server logic.

Now you may get following error message:

This is because the result data set which is return, is to big to handle. We have to increase the maxBufferSize and maxReceivedMessageSize in the app.config:

<basicHttpBinding> <binding name="ProjectSoap" closeTimeout="00:01:00" openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00" allowCookies="false" bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard" maxBufferSize="10485760" maxBufferPoolSize="524288" maxReceivedMessageSize="10485760" messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered" useDefaultWebProxy="true"> ...

I always set this value to 10485760 bytes which are exactly 10 MB! After setting this value we should get our project informations.

In the table ProjectCustomFields are all custom fields listed which are related to this project. Every custom field has some properties:

  • CUSTOM_FIELD_UID (UID of the custom field in this project)
  • PROJ_UID (project UID which contains this custom field)
  • MD_PROP_UID (UID of the custom field definition (Enterprise Objects))
  • MD_PROP_ID (the same as an integer)
  • FIELD_TYPE_ENUM (field type number)
  • … (fields for storing direct values like strings, dates, integer)

If we want to know how these custom fields are named, we have to use the customfieldSvc to get the definitions of these customfields. Here is a little example to lookup the customfield names:

//getting customfield definitions CustomFieldDataSet fieldDefs = customfieldSvc.ReadCustomFields(string.Empty, false); foreach (ProjectDataSet.ProjectCustomFieldsRow cField in myProject.ProjectCustomFields.Rows) { CustomFieldDataSet.CustomFieldsRow fieldDefinition = fieldDefs.CustomFields.Single( cfd => cfd.MD_PROP_UID == cField.MD_PROP_UID); Console.WriteLine(fieldDefinition.MD_PROP_NAME); }

Now we now how these fields are called. If these fields are number fields it’s very easy to get the values. Just read the type of the field (if its an integer, the type value is 15):

foreach (ProjectDataSet.ProjectCustomFieldsRow cField in myProject.ProjectCustomFields.Rows) { CustomFieldDataSet.CustomFieldsRow fieldDefinition = fieldDefs.CustomFields.Single( cfd => cfd.MD_PROP_UID == cField.MD_PROP_UID); if (cField.FIELD_TYPE_ENUM == 15) { Console.WriteLine("Name: " + fieldDefinition.MD_PROP_NAME); Console.WriteLine("Value: " + cField.NUM_VALUE); } }

You can also do this for TEXT, DATE & as shown NUMBERS. This is very simple because we just have to lookup over one table if we need the name of the field. If we have the Guid, we just need the Project Web service to get the values.

Lookup a Choice Field

A little bit harder is the query to get a value of a choice field. To understand this, I’ve “designed” a very simple entity diagram:

So the way is easy:

  1. we have to get the custom field definition via the MD_PROP_UID
  2. the definition tells us that this is a lookup field and in which table the field is
  3. over the lookup table we can get the lookup trees (all possible values)
  4. now it’s easy to find the right tree row with the CODE_VALUE from our custom field

And here is the code for this:

else if (cField.FIELD_TYPE_ENUM == 21) { LookupTableDataSet lookupset = loockuptableSvc.ReadLookupTablesByUids( new Guid[] { fieldDefinition.MD_LOOKUP_TABLE_UID }, false, 1031); LookupTableDataSet.LookupTableTreesRow treeRow = lookupset.LookupTableTrees.FindByLT_STRUCT_UID(cField.CODE_VALUE); Console.WriteLine("Name: " + fieldDefinition.MD_PROP_NAME); Console.WriteLine("Value: " + treeRow.LT_VALUE_TEXT); }

Now we can read every value in our custom fields!

You can learn how to write data here…

Source Code of the whole project:

Download

 

Comments are now closed for this article

  • gk - 11 years ago

    Great article! helped me a lot

  • Andrej - 11 years ago

    Hi,

    Great article!

    I’m having only one problem on the line
    ProjectDataSet projectList = projectSvc.ReadProjectList();

    Error message: Unhandled Communication Fault occurred

    Any ideas?

    • CommanderCody - 10 years ago

      ReadProjectList() requires a great deal of rights for the account being used to access the PSI. You can use ReadProjectStatus() instead, which will give you projects the user account can see, without requiring such privileges.

  • CommanderCody - 10 years ago

    The PSI definitely has some oddities when getting to Custom Fields, and some of the other datasets in the object model. That you so much for this! This is extremely helpful information for any PSI developer trying to extract data from Project server via Web Services and not via a DB query or a Business Intelligence Report.

  • Deepali - 10 years ago

    Thank you so much.. this post helped me a lot..

  • Michael - 10 years ago

    Hi,
    great article!
    I have a question that does not completely refer to this article.
    May-be you have an answer? I need to read the approved actual work for all projects / all tasks within a week. Do you have a hint for me where to start? And do I need some Admin-User-Account to do that?
    Thanks in advance!

    Michael

  • Daniel.com - 9 years ago

    Hi, Do you know how to get ONLY the project custom fields (not the resource nor tasks custom fields) from the CustomField datasource obtanied by using the ReadCustomFields method?

    Thanks
    Daniel

  • Niteesh Kumar - 9 years ago

    Hiii,
    I have a problem in Read,update,delete,create Project Server 2010 project and lookuptable over PSI

  • Shari - 9 years ago

    Hi,

    I’m new to this project and seems like no one in my office is aware of the column names since the project was closed long back. but now for anew requirement i need to find the column names in the project server. please help.

Pings & Trackbacks