Second Life of a Hungarian SharePoint Geek

July 26, 2016

How to Invoke PSI Methods using PSContext, Demonstrated by an Example of Reading Delegation Information

Filed under: PS 2013, PSI, Reflection — Tags: , , — Peter Holpar @ 21:21

The Challange

So you may ask now, why would we like to access PSI functionality from PSContext at all? Let me explain our situation.

Recently we had to extend an existing server side code in our custom Project Server solution to enable delegate users (that means ones acting for another users at the given time) to access the same custom functionality as the user they acting for.

Note: You can manage delegation via PWA Settings / Manage Delegates (either in the Personal Setting or in the Security section), see the page http://YourProjectServer/PWA/_layouts/15/pwa/userdelegation/ManageDelegations.aspx?mgm=true.

To make the things event worse, we had to check the delegation information in code running in the SharePoint web context, and in code that runs as a simple server side process without web context. It is actually not a timer job, but an automated server process scheduled by Windows Task Scheduler for the sake of simplicity, that perform some kind of housekeeping and reporting tasks for the custom application.However, in the case of a time job, an asynchronous event receiver, or a workflow step running without web context, you would have the same issue. Why these two cases (with or without SharePoint web context) differ from each other, will be discussed later in more details.

You probably know, that although the server side object model of the Project Server (available via the Microsoft.ProjectServer.PSContext) covers quite a lot of the functionality of the PSI, there is still a plenty of features that are simply inaccessible via the objects available through PSContext. For example, there is no way to access the delegation information. That means for us, that we should read this information via PSI, as its Resource class provides a ReadDelegations method just for this purpose. If you are not new in Project Server development, you should have already your own experience with PSI. If you have not any experience with that yet, I can say you, that it is rather complex comparing to the server side OM provided by PSContext. You should work with various DataSet and DataTable objects, but the greatest problem in our case that it requires a lot of configuration, either by config files (see the Configuring the Services with app.config section of the Walkthrough: Developing PSI Applications Using WCF article for example) , or via code (see section 11 of  the Walkthrough: Developing PSI Applications Using WCF article, or the Configuring the Services Programmatically section of the Walkthrough: Developing PSI Applications Using WCF article for example), just to be able to start using the PSI web services. If you have more applications (in our case one with, and another one without SharePoint web context), multiple environments (development, test, and production, one with HTTP, another one with HTTPS), and multiple servers in the environments, creating and maintaining the configuration information is typically not a trivial task.

Why could not we simply access the PSI endpoints via the PSContext? It would be great, wouldn’t it? Are you surprised if I say, it is possible? Well, actually it is. It requires an amount of hacking, and probably not a supported solution, but it is technically possible, and it made our life easier. If you take the risk on your own, you can try it as well, however there is no guarantee, that it works for you, or that once it works, a new Project Server patch could not break the functionality. So be warned.

After this introduction you should have already an overview of the problem and hopefully you are ready to read about the technical stuff. If you are not interested in such details, and need only the result of the analysis, you can skip the next two sections.

Connection between the Project Server client side object model and the server side object model

If one dig into the assemblies implementing the server side object model (Microsoft.Project.dll), and the client side object model (Microsoft.Project.Client.dll) of Project Server, then it turns out, that Microsoft has not re-implemented the functionality of PSI, these libraries still utilize the good-old PSI infrastructure either directly (in case of the server side OM) or indirectly (in case of the client side OM, that calls PSI via the server side OM).

How the client side OM invokes the server side OM is not Project Server specific, the infrastructure is inherited from SharePoint. Although there were changes between the SharePoint versions 2010 and 2013, the main concepts remain the same. Both the server side and client side of that bridge were deeply researched and analyzed in my posts five years ago. Those posts should give you enough insight into the internal functionality of the client side OM, this topic is outside of scope of the current post.

It is more exciting (at least, for now), how the Project Server server side OM invokes the PSI infrastructure. Exactly that will I describe in the next section.

Connection between the Project Server server side object model and PSI

The Microsoft.Office.Project.PWA namespace contains a public class called PSI (implemented in assembly Microsoft.Office.Project.Server.PWA). This class exposes more than 20 public properties with postfix “WebService”, each of them returns the WCF proxy of the corresponding PSI channel. For example, the ResourceWebService property returns a reference for a Resource object (namespace: Microsoft.Office.Project.Server.WebServiceProxy, assembly: Microsoft.Office.Project.Server.PWA), inherited from WcfProxyBase<IResource>. Once created in the “WebService” property getters, these WCF proxy objects are stored in the private field _proxyContainer (of  type ProxyContainer, implemented as a nested class) of the PSI class for any further access.

For the purpose of our solution, it is irrelevant how the WCF proxy objects (like the Resource object returned by the ResourceWebService property of the PSI class) know, what configuration they should use and how they can access the WCF endpoint. Probably I write about sometimes later in an other post.

The internal PJClientCallableContext class (Microsoft.ProjectServer namespace, Microsoft.ProjectServer assembly) contains a static read-only property called PJContext. It returns an instance of the PJContext class (Microsoft.Office.Project.PWA namespace, Microsoft.Office.Project.Server.PWA assembly) using the generic private static RetrieveValue method. If the process runs in the HTTP context (HttpContext.Current is not null) the RetrieveValue method invokes the GetContext(bool isWebServiceCall, bool ignoreCachedContext) method of the PJContext class. Otherwise (without HTTP context), the lazy-initialized value of the _pjcontext field will be returned by RetrieveValue method. The lazy-initialization of this field can be found in the Initialize method of the PJClientCallableContext class, the static GetObjectModelContext method of the PJContext class is invoked using the SPWeb object instance passed to the Initialize method as parameter. This is the same SPWeb object, that you passed to the constructor of the PSContext object (or it is the RootWeb of the site if you used the PSContext constructor with the SPSite object; or the RootWeb of the site corresponding to the URL if you used the PSContext constructor with the Uri object), as in the PSContext(SPWeb web) constructor an instance of the PJClientCallableContext class is created using the web instance as parameter.

Side note: On the other hand, using the PSContext constructors with parameters in a HTTP context may result you do no get the expected outcome, but it is beyond our scope again, more on that in a later post.

After this theoretical explanation let’s see some functional code.

The Code

My goal was to access the static PJContext property of the internal PJClientCallableContext class via Reflection, and expose its PSI property to my code as an extension method.

The solution described below requires adding the following assembly references:

  • Microsoft.Project.dll
  • Microsoft.Office.Project.Server.Library.dll
  • Microsoft.Office.Project.Server.PWA.dll
  • Microsoft.Office.Project.Schema.dll
  • Microsoft.Office.Project.Server.Administration.dll

The extension method is rather simple, and requires this using directive to work:

using Microsoft.Office.Project.PWA;

  1. public static PSI GetPSI(this PSContext dummyContext)
  2. {
  3.     PSI result = null;
  4.     Assembly psAssembly = typeof(PSContext).Assembly;
  5.  
  6.     // get internal type
  7.     Type type_PJClientCallableContext = psAssembly.GetType("Microsoft.ProjectServer.PJClientCallableContext");
  8.     PropertyInfo pi_PJContext = type_PJClientCallableContext.GetProperty("PJContext");
  9.     PJContext pjContext = pi_PJContext.GetValue(null) as PJContext;
  10.  
  11.     if (pjContext != null)
  12.     {
  13.         result = pjContext.PSI;
  14.     }
  15.  
  16.     return result;
  17. }

Note: As you can see, in the code above we don’t use the PSContext object passed as parameter to the extension method at all. It is only to enable attaching the functionality to a PSContext instance via the extension method, and so enforcing the creation of a PSContext instance first. If you have an application without HTTP context (like a console application), and you call this code without creating a PSContext instance first, like:

PSI psi = Extensions.GetPSI(null);

an InvalidOperationException will be thrown by the RetrieveValue method of the PJClientCallableContext class:

You must wrap your server OM code in a PJClientCallableContext when outside of an HttpContext.

In the case of a web application you don’t have such problem, as the PJContext instance is created using the current HTTP context.

Below I illustrate the usage of the extension method, by querying the delegation information of an enterprise resource in Project Server. To achieve that, I invoke the ReadDelegations method of the Resource class. See the available values of the DelegationFilter enumeration in the documentation.

The code requires the following using directives:

using Microsoft.Project;
using Microsoft.Office.Project.PWA;
using schema = Microsoft.Office.Project.Server.Schema;
using lib = Microsoft.Office.Project.Server.Library;

  1. using (PSContext projectContext = PSContext.GetContext(new Uri(pwaUrl)))
  2. {
  3.     PSI psi = projectContext.GetPSI();
  4.                     
  5.     lib.UserDelegationConsts.DelegationFilter filter = lib.UserDelegationConsts.DelegationFilter.All;
  6.  
  7.     // replace the GUID with an ID of a resource in your environment
  8.     // whose delegation information you would like to display
  9.     Guid resId = Guid.Parse("087ade95-281e-e411-9568-005056b45654");
  10.  
  11.     using (schema.UserDelegationDataSet delegationDS = psi.ResourceWebService.ReadDelegations(filter, resId))
  12.     {
  13.         foreach (schema.UserDelegationDataSet.ResourceDelegationsRow resDelegation in delegationDS.ResourceDelegations)
  14.         {
  15.             Console.WriteLine("Resource is substituted by '{0}' from '{1}' to '{2}'", resDelegation.DELEGATE_NAME, resDelegation.DELEGATION_START, resDelegation.DELEGATION_FINISH);
  16.         }
  17.     }
  18. }

Using the approach above you don’t have to bother with the configuration of the PSI proxy you need in your application, and still access such features in your server side code, that are available in PSI.

Note: There is a major restriction using this method. Although the PSI web services are available from a client computer, or from a remote server as well, you can not access remote servers using the code above. You can access only the PSI resources exposed by the local farm.

Advertisements

April 20, 2016

Reusing PSI Proxy Objects from PowerShell

Filed under: PowerShell, PS 2013, PSI — Tags: , , — Peter Holpar @ 22:06

Assume you create a PowerShell script that invokes PSI to perform some actions on Project Server. For example, creating a custom field as described in my former post. You save the script as a .ps1 file and invoke it from the PowerShell shell. Assume it has some parameters and your goal is to invoke it multiple times with various parameter sets. On of the first step in the script is of course the creation in the PSI proxy object, as shown in the original version:

$pwaUrl = "http://YourProjectServer/pwa&quot;
$svcPath = "/_vti_bin/psi/CustomFields.asmx?wsdl"

$svcPSProxy = New-WebServiceProxy -Namespace PSIProxy -Uri ($pwaUrl + $svcPath) -UseDefaultCredential

later in your code you invoke a method on the proxy object:

$svcPSProxy.CreateCustomFields($customFieldDataSet, $false, $true)

On the first run of the script it performs the actions without error, however on the next (and on each later) run it gives you an exception like this:

Cannot convert argument "cfds", with value: "PSIProxy.CustomFieldDataSet", for
"CreateCustomFields" to type "PSIProxy.CustomFieldDataSet": "Cannot convert
the "PSIProxy.CustomFieldDataSet" value of type "PSIProxy.CustomFieldDataSet"
to type "PSIProxy.CustomFieldDataSet"."
At line:1 char:1
+ $svcPSProxy.CreateCustomFields($customFieldDataSet, $false, $true)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument

Do you see the strange error message?

Cannot convert the "PSIProxy.CustomFieldDataSet" value of type "PSIProxy.CustomFieldDataSet" to type "PSIProxy.CustomFieldDataSet".

An object having a specific type cannot be converted to a type having the same type name. Very weird. PowerShell seems to cache the object types created dynamically by the New-WebServiceProxy cmdlet on the first run, and these types seem to be not compatible (at least, in the .NET-sense) with the ones created on the next runs. The single (or at least the most simple) solution seems to be to restart the shell after each run, but it is not very nice, to say the least.

Fortunately, I’ve found a better way in this thread for the “recycling” of the proxy object created on the first execution. Note, that the solution I find there is not the accepted answer as I wrote this post. See the answer from existenz7 on February 07, 2013 1:08 PM.

So I’ve changed the proxy creation part in my script to the form:

If ($global:svcPSProxy -eq $null)
{
  Write-Host "Connecting PSI proxy at $pwaUrl …"
  $global:svcPSProxy = New-WebServiceProxy -Namespace PSIProxy -Uri ($pwaUrl + $svcPath) -UseDefaultCredential
}
Else
{
  Write-Host "Reusing existing PSI proxy"
}

You can invoke the proxy method just like earlier:

$svcPSProxy.CreateCustomFields($customFieldDataSet, $false, $true)

Note: I typically omit this kind of proxy creation from my code posted here on the blog just not to disturb you with details that are not relevant to the problem discussed actually in the post. However, I suggest you to apply the same technique to avoid the type incompatibility issue mentioned above.

Handling PSI Errors in PowerShell Scripts

Filed under: PowerShell, PS 2013, PSI — Tags: , , — Peter Holpar @ 22:03

Recently I work pretty much with PSI calls from PowerShell, to automate such administrative tasks, that are not available in the Project Server Client Object Model, like setting the rules of graphical indicators for the custom fields (see full code here). When using such code, sooner or later you receive an PSI exception, either due to lack of data, an invalid data, or simply because the entity you are working with are not checked out to you.

For example, if you call the CreateCustomFields as shown in the former post:

$svcPSProxy.CreateCustomFields($customFieldDataSet, $false, $true)

you may receive a PSI error that is displayed by default so in PowerShell:

Exception calling "CreateCustomFields" with "3" argument(s):
"ProjectServerError(s) LastError=CustomFieldLowerOrderBitsOutOfRange
Instructions: Pass this into PSClientError constructor to access all error
information"
At line:1 char:1
+ $svcPSProxy.CreateCustomFields($customFieldDataSet, $false, $true)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SoapException

To display the details of the error, I’ve implemented the error handling similar to the one you find in several PSI-related C# article on MSDN (see this one, for example):

Try {
    $svcPSProxy.CreateCustomFields($customFieldDataSet, $false, $true)
}
Catch [System.Web.Services.Protocols.SoapException] {
    $ex = $_.Exception
    $clientError = New-Object Microsoft.Office.Project.Server.Library.PSClientError($ex)   
    $psiErrors = $clientError.GetAllErrors()
    $psiErrors | % {
        $err = $_
        Write-Host ([int]$err.ErrId) $err.ErrName
        For($i = 0; $i -lt $err.ErrorAttributes.Length; $i++) {
            Write-Host $err.ErrorAttributeNames()[$i] $err.ErrorAttributes[$i];
        }
    }
}

In my case, the specific error information is displayed as error code – error name pairs, like:

11521 CustomFieldMaskDoesNotMatchEntityType
11522 CustomFieldLowerOrderBitsOutOfRange

Note 1: I typically omit this kind of error handling from my code posted here on the blog just not to disturb you with details that are not relevant to the problem discussed actually in the post. It may be OK for you to run the code as it is posted as long as there is no error, however if an exception is thrown, it is good to know how to access the details of the problem. Alternatively, you can use Fiddler to capture the server response, and check the raw XML for the error information.

Note 2: A comprehensive list of PSI error codes is available here, including a short description for each error type. See the Microsoft.Office.Project.Server.Library.PSErrorID enumeration as well.

July 23, 2015

Managing Project Server Views via PSI from PowerShell

Filed under: ALM, PowerShell, Project Server, PSI — Tags: , , , — Peter Holpar @ 07:17

If you would like to manage Project Server views from code you will find very few helpful resources (if any) on the web. The object models simply do not include classes related to this (neither on the server side nor on the client side). Although the PSI contains a View service, it is intended for internal use. Of course, that intention could not stop us to use the service at our own risk. Below I give you some useful code samples to illustrate the usage of the View service.

First of all, we create the proxy assembly, load the required Microsoft.Office.Project.Server.Library assembly in the process as well, and define some shortcuts to make it easier to reference enum and property values later on.

$pwaUrl = "http://YourProjectServer/pwa&quot;
$svcPSProxy = New-WebServiceProxy -Namespace PSIProxy -Uri ($pwaUrl + "/_vti_bin/PSI/View.asmx?wsdl") -UseDefaultCredential
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Project.Server.Library")
$ViewConstants = [Microsoft.Office.Project.Server.Library.ViewConstants]
$ViewType = [Microsoft.Office.Project.Server.Library.ViewConstants+ViewType]

If you now the unique ID of your view, it is easy to display all of the fields and security categories associated with the view:

$viewId = [Guid]"63d3499e-df27-401c-af58-ebb9607beae8"
$view = $svcPSProxy.ReadView($viewId)
$view.ViewReportFields | % { $_.CONV_STRING }
$view.SecurityCategoryObjects | % { $_.WSEC_CAT_NAME }

If the view ID is unknown, you can get it based on the name and type of the view:

$viewName = "Your Report"
$viewType = $ViewType::PORTFOLIO

$views = $svcPSProxy.ReadViewSummaries()
$viewId = ($views.ViewReports | ? { $_.WVIEW_NAME -eq $viewName -and $_.WVIEW_TYPE -eq $viewType }).WVIEW_UID

You can list all of the views:

$views = $svcPSProxy.ReadViewSummaries()
$views.ViewReports | % {
  Write-Host $_.WVIEW_NAME ($ViewType$_.WVIEW_TYPE)
}

To change the order of the first two fields in the view:

$view = $svcPSProxy.ReadView($viewId)
$view.ViewReportFields[0].WVIEW_FIELD_ORDER = 1
$view.ViewReportFields[1].WVIEW_FIELD_ORDER = 0
$svcPSProxy.UpdateView($view)

To change the order of two arbitrary fields (based on their name) in the view:

$fieldName1 = "Finish"
$fieldName2 = "Owner"
$view = $svcPSProxy.ReadView($viewId)
$field1 = $view.ViewReportFields | ? { $_.CONV_STRING -eq $fieldName1 }
$field2 = $view.ViewReportFields | ? { $_.CONV_STRING -eq $fieldName2 }
$field1Order = $field1.WVIEW_FIELD_ORDER
$field2Order = $field2.WVIEW_FIELD_ORDER
$field1.WVIEW_FIELD_ORDER = $field2Order
$field2.WVIEW_FIELD_ORDER = $field1Order
$svcPSProxy.UpdateView($view)

To remove a field from a view:

$fieldToRemoveName = "Ende"
$view = $svcPSProxy.ReadView($viewId)
$fieldToRemove = $view.ViewReportFields | ? { $_.CONV_STRING -eq $fieldToRemoveName }
$fieldToRemove.Delete()
$svcPSProxy.UpdateView($view)

To delete the view itself:

[Void]$svcPSProxy.DeleteViewReports($viewId)

To create a new view using an existing view as a template:

$newViewName = "New View"
[Void]$svcPSProxy.CopyViewReports($viewId, $newViewName)
$newView = $svcPSProxy.ReadViewSummaries().ViewReports | ? { $_.WVIEW_NAME -eq $newViewName -and $_.WVIEW_TYPE -eq $viewType }

To list all of the fields available in a given type (in this case, for tasks):

$svcPSProxy.ReadProjectFields($ViewConstants::ViewTABLE_TASK_UID ).ViewFields | % { $_.CONV_STRING }

To append a new field at the end of the fields in the view:

$fieldToAppendName = "% Work Complete"

$fieldToAppend = $svcPSProxy.ReadProjectFields($ViewConstants::ViewTABLE_TASK_UID ).ViewFields | ? { $_.CONV_STRING -eq $fieldToAppendName }
$view = $svcPSProxy.ReadView($viewId)
$maxFieldOrder = ($view.ViewReportFields | % { $_.WVIEW_FIELD_ORDER } | measure -Maximum).Maximum

$newField = $view.ViewReportFields.NewViewReportFieldsRow()

$newField.WFIELD_UID = $fieldToAppend.WFIELD_UID
$newField.CONV_STRING = $fieldToAppend.CONV_STRING
$newField.WFIELD_TEXTCONV_TYPE = $fieldToAppend.WFIELD_TEXTCONV_TYPE
$newField.WTABLE_UID = $fieldToAppend.WTABLE_UID
$newField.WFIELD_IS_CUSTOM_FIELD = $fieldToAppend.WFIELD_IS_CUSTOM_FIELD
$newField.WFIELD_NAME_SQL = $fieldToAppend.WFIELD_NAME_SQL
$newField.WFIELD_IS_MULTI_VALUE = $fieldToAppend.WFIELD_IS_MULTI_VALUE
$newField.WFIELD_LOOKUP_TABLE_UID = $fieldToAppend.WFIELD_LOOKUP_TABLE_UID
$newField.WVIEW_UID = $view.ViewReports.WVIEW_UID
$newField.WVIEW_FIELD_ORDER = $maxFieldOrder + 1
$newField.WVIEW_FIELD_WIDTH = 100
$newField.WVIEW_FIELD_AUTOSIZE = 1
$newField.WVIEW_FIELD_CUSTOM_LABEL = [System.DBNull]::Value
$newField.WVIEW_FIELD_IS_READ_ONLY = 0

$view.ViewReportFields.AddViewReportFieldsRow($newField)
$svcPSProxy.UpdateView($view)

To inject a new field in the view before another field having a specified name:

$fieldInjectBeforeName = "% Complete"
$fieldToInjectName = "% Work Complete"

$fieldToInject = $svcPSProxy.ReadProjectFields($ViewConstants::ViewTABLE_TASK_UID ).ViewFields | ? { $_.CONV_STRING -eq $fieldToInjectName }

$view = $svcPSProxy.ReadView($viewId)

$fieldInjectBeforeOrder = ($view.ViewReportFields | ? { $_.CONV_STRING -eq $fieldInjectBeforeName }).WVIEW_FIELD_ORDER

$view.ViewReportFields | ? { $_.WVIEW_FIELD_ORDER -ge $fieldInjectBeforeOrder } | % { $_.WVIEW_FIELD_ORDER++ }

$newField = $view.ViewReportFields.NewViewReportFieldsRow()

$newField.WFIELD_UID = $fieldToInject.WFIELD_UID
$newField.CONV_STRING = $fieldToInject.CONV_STRING
$newField.WFIELD_TEXTCONV_TYPE = $fieldToInject.WFIELD_TEXTCONV_TYPE
$newField.WTABLE_UID = $fieldToInject.WTABLE_UID
$newField.WFIELD_IS_CUSTOM_FIELD = $fieldToInject.WFIELD_IS_CUSTOM_FIELD
$newField.WFIELD_NAME_SQL = $fieldToInject.WFIELD_NAME_SQL
$newField.WFIELD_IS_MULTI_VALUE = $fieldToInject.WFIELD_IS_MULTI_VALUE
$newField.WFIELD_LOOKUP_TABLE_UID = $fieldToInject.WFIELD_LOOKUP_TABLE_UID
$newField.WVIEW_UID = $view.ViewReports.WVIEW_UID
$newField.WVIEW_FIELD_ORDER = $fieldInjectBeforeOrder
$newField.WVIEW_FIELD_WIDTH = 100
$newField.WVIEW_FIELD_AUTOSIZE = 1
$newField.WVIEW_FIELD_CUSTOM_LABEL = [System.DBNull]::Value
$newField.WVIEW_FIELD_IS_READ_ONLY = 0

$view.ViewReportFields.AddViewReportFieldsRow($newField)
$svcPSProxy.UpdateView($view)

The last code sample shows how to create a new Gantt-view from scratch, appending a single field and a single security category to it:

$viewRepDS = New-Object PSIProxy.PWAViewReportsDataSet
$newView = $viewRepDS.ViewReports.NewViewReportsRow()
$newView.WVIEW_UID = [Guid]::NewGuid()
$newView.WVIEW_NAME = "New Report 2"
$newView.WVIEW_DESCRIPTION = "Test report description"

$fieldToAppendName = "% Arbeit abgeschlossen"

$fieldToAppend = $svcPSProxy.ReadProjectFields($ViewConstants::ViewTABLE_TASK_UID ).ViewFields | ? { $_.CONV_STRING -eq $fieldToAppendName }

$newField = $viewRepDS.ViewReportFields.NewViewReportFieldsRow()

$newField.WFIELD_UID = $fieldToAppend.WFIELD_UID
$newField.CONV_STRING = $fieldToAppend.CONV_STRING
$newField.WFIELD_TEXTCONV_TYPE = $fieldToAppend.WFIELD_TEXTCONV_TYPE
$newField.WFIELD_IS_CUSTOM_FIELD = $fieldToAppend.WFIELD_IS_CUSTOM_FIELD
$newField.WFIELD_NAME_SQL = $fieldToAppend.WFIELD_NAME_SQL
$newField.WFIELD_IS_MULTI_VALUE = $fieldToAppend.WFIELD_IS_MULTI_VALUE
$newField.WFIELD_LOOKUP_TABLE_UID = $fieldToAppend.WFIELD_LOOKUP_TABLE_UID
$newField.WVIEW_UID = $newView.WVIEW_UID
$newField.WVIEW_FIELD_ORDER = 0
$newField.WVIEW_FIELD_WIDTH = 100
$newField.WVIEW_FIELD_AUTOSIZE = 1
$newField.WVIEW_FIELD_CUSTOM_LABEL = [System.DBNull]::Value
$newField.WVIEW_FIELD_IS_READ_ONLY = 0
$viewRepDS.ViewReportFields.AddViewReportFieldsRow($newField)

$newSecCat = $viewRepDS.SecurityCategoryObjects.NewSecurityCategoryObjectsRow()
$newSecCat.WSEC_CAT_UID = [Microsoft.Office.Project.Server.Library.PSSecurityCategory]::MyProjects
$newSecCat.WSEC_OBJ_TYPE_UID = [Microsoft.Office.Project.Server.Library.PSSecurityObjectType]::View
$newSecCat.WSEC_OBJ_UID = $newView.WVIEW_UID
$viewRepDS.SecurityCategoryObjects.AddSecurityCategoryObjectsRow($newSecCat)

$newView.WVIEW_TYPE = $ViewType::PORTFOLIO
$newView.WVIEW_DISPLAY_TYPE = $ViewConstants::ViewDISPLAYTYPE_GANTT
$newView.WGANTT_SCHEME_UID =  $ViewConstants::GanttSchemeUidProjectCenter
$newView.WVIEW_SPLITTER_POS = 250
#  Group by (see [pub].[MSP_WEB_GROUP_SCHEMES] table in Project DB for possible values)
$newView.WGROUP_SCHEME_UID = [Guid]::Empty

$viewRepDS.ViewReports.AddViewReportsRow($newView)
$svcPSProxy.UpdateView($viewRepDS)

July 22, 2015

Create Project Server Enterprise Custom Fields via PSI from PowerShell

Filed under: ALM, PowerShell, Project Server, PSI — Tags: , , , — Peter Holpar @ 22:38

Last year I already wrote about how one can manage the Project Server Enterprise Custom Fields via the Managed Client Object Modell. We could transfer the code samples of that post from C# to PowerShell, but because of the limitations of the Managed Client Object Modell I use the PSI interface instead in this case. What are those limitations? Not all of the properties available in PSI are exposed by the Client OM, see for example the MD_PROP_SUMM_GRAPHICAL_INDICATOR field, that we can use to set the rules of graphical indicators defined for the fields. I’ll show you an example for getting and setting the indicator rules in a later post, in the current one I only show you the technique we can use to create the Enterprise Custom Fields via PSI.

One can find an existing description with code sample in Step 3 and 4 of this post, that achieves the same goal, however, I don’t like that approach for several reasons, for example, because of  we have to generate the proxy assembly based on the WSDL in the code itself. Instead of that I find the following code much more simple:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Project.Server.Library")

$pwaUrl = "http://YourProjectServer/pwa&quot;

# create shortcuts
#
http://stackoverflow.com/a/1049010
$PSDataType = [Microsoft.Office.Project.Server.Library.PSDataType]
$Entities = [Microsoft.Office.Project.Server.Library.EntityCollection]::Entities

$svcPSProxy = New-WebServiceProxy -Namespace PSIProxy -Uri ($pwaUrl + "/_vti_bin/psi/CustomFields.asmx?wsdl") -UseDefaultCredential

$customFieldDataSet = New-Object PSIProxy.CustomFieldDataSet 

$customFieldRow = $customFieldDataSet.CustomFields.NewCustomFieldsRow()   
$customFieldRow.MD_PROP_UID = [Guid]::NewGuid()
$customFieldRow.MD_PROP_NAME = "Custom Project Field"
$customFieldRow.MD_PROP_TYPE_ENUM = $PSDataType::STRING
$customFieldRow.MD_ENT_TYPE_UID = $Entities.ProjectEntity.UniqueId
$customFieldRow.MD_PROP_IS_REQUIRED = $false
$customFieldRow.MD_PROP_IS_LEAF_NODE_ONLY = $false
$customFieldRow.MD_PROP_DESCRIPTION = "Test Field Desc."
$customFieldRow.SetMD_LOOKUP_TABLE_UIDNull()
$customFieldRow.SetMD_PROP_DEFAULT_VALUENull()
$customFieldDataSet.CustomFields.AddCustomFieldsRow($customFieldRow)

$svcPSProxy.CreateCustomFields($customFieldDataSet, $false, $true)

If you have casting issues when using the Namespace parameter of the New-WebServiceProxy cmdlet, you should read this post.

July 16, 2015

How to Read Project Properties that are not Available in the Client Object Model?

Recently I had a development task that at the first sight seemed to be trivial, but it turned out quickly to be rather a challenge. I had to display some basic project information on a page in our Project Web Site (PWS), like project start and finish date, remaining work and percent complete. The web page was built using client-side technologies, like the Client-side object model (CSOM) for Project 2013 and using the AngularJS library, and we did not plan to change the architecture to server side code.

If you check the properties of the PublishedProject (either on the client side in namespace / assembly Microsoft.ProjectServer.Client or on the server side in Microsoft.ProjectServer), you see that it has properties like StartDate and FinishDate, and it inherits its PercentComplete property from the Project base class, however there is no property for RemainingWork or PercentWorkComplete defined, although both of these values are available as fields if you manage a Project Server view (see screenshot below). This information is not available via REST / OData either.

image

You should know, that in the case of  Project Server, the server side OM is simply a wrapper around the PSI, for example, the PercentComplete property in the Project class is defined:

public int PercentComplete
{
  get
  {
    ProjectDataSet.TaskRow summaryTaskRow = this.SummaryTaskRow;
    if (summaryTaskRow != null && !summaryTaskRow.IsTASK_PCT_COMPNull())
      return summaryTaskRow.TASK_PCT_COMP;
    else
      return 0;
  }
}

Client side OMs (either managed or ECMAScript) and REST calls invoke the server side OM, so at the background the good old PSI is still in action.

It seems that the developers of Project Server remained simply not enough time to map all of the fields available via PSI to the object models on the server side and the client side.

You should know either, that the project properties we need are stored as task properties for the project summary task of the current project. In the Project Server database the tasks of the published projects (so the project summary tasks as well) are stored in the [pub].[MSP_TASKS] table. If you run the following query (where ProjectWebApp is the name of the database and the Guid in the [PROJ_UID] filter is the ID of your project), you find some specific field values that may help to identify the summary task record of a project:

SELECT [TASK_UID]    
      ,[TASK_PARENT_UID]
      ,[TASK_ID]
      ,[TASK_OUTLINE_NUM]
      ,[TASK_OUTLINE_LEVEL]
      ,[TASK_NAME]
      ,[TASK_START_DATE]
      ,[TASK_FINISH_DATE]
      ,[TASK_PCT_COMP]
      ,[TASK_PCT_WORK_COMP]
      ,[TASK_REM_WORK]
  FROM [ProjectWebApp].[pub].[MSP_TASKS]
  WHERE [PROJ_UID] = ‘d0ae5086-be7a-e411-9568-005056b45654’

The project summary task record – at least, based on my experimental results – , matches the following conditions:

[TASK_ID] = 0

[TASK_OUTLINE_NUM] = 0

[TASK_OUTLINE_LEVEL] = 0

[TASK_UID] = [TASK_PARENT_UID]

But as said, we need a solution on the client side, and obviously one that does not tamper with the Project Server database. What options are there to achieve the missing information?

The Project class has a property called SummaryTaskId, but if you have this value already, and would like to query the project tasks via REST (for example: http://YourProjServer/PWA/_api/ProjectServer/Projects(‘d0ae5086-be7a-e411-9568-005056b45654&#8217;)/Tasks(‘FFAE5086-BE7A-E411-9568-005056B45654’)) or via the client object model, the result is empty. The description of the SummaryTaskId property says: “Gets the GUID for the hidden project summary task”. Yes, it is so hidden, that it simply not included in the Tasks collection of the Project class! The Tasks property of the PublishedProject class is of type PublishedTaskCollection, and on the server side the record for the project summary task is simply filtered out, when initializing the internal Dictionary used for the storage of the Task records. If you don’t believe me, or need more details on that, see the constructor method of Microsoft.ProjectServer.PublishedTaskCollection class below:

internal PublishedTaskCollection()
{
    Func<Dictionary<Guid, PublishedTask>> valueFactory = null;
    if (valueFactory == null)
    {
        valueFactory = () => base.ProjectData.Task.OfType<ProjectDataSet.TaskRow>().Where<ProjectDataSet.TaskRow>(delegate (ProjectDataSet.TaskRow r) {
            if (!r.IsTASK_PARENT_UIDNull())
            {
                return (r.TASK_PARENT_UID != r.TASK_UID);
            }
            return true;
        }).ToDictionary<ProjectDataSet.TaskRow, Guid, PublishedTask>(r => r.TASK_UID, r => this.CreateTask(r));
    }
    this._tasks = new Lazy<Dictionary<Guid, PublishedTask>>(valueFactory);
}

Of course, we get the same, empty result if we would like to filter the tasks for one the special conditions we found in the database (like [TASK_OUTLINE_LEVEL] = 0):
http://YourProjServer/PWA/_api/ProjectServer/Projects(‘d0ae5086-be7a-e411-9568-005056b45654&#8217;)/Tasks?$filter=OutlineLevel eq 0 

The project reporting data contains the project summary tasks as well, so we could invoke the ProjectData OData endpoint from the client side to query the required information. The problem with this approach is that it would require extra permissions on the reporting data and one cannot limit this permission to the summary tasks of a specific project, to summary tasks, or just to tasks at all. If you grant your users the Access Project Server Reporting Service global permission, they can query all of the reporting data. It is sure not our goal, but you can test it if you wish.

Once you have the ID of the project summary task (for example via the SummaryTaskId property), the task is available via a query like this one:

http://YourProjServer/PWA/_api/ProjectData/Tasks(ProjektID=guid’d0ae5086-be7a-e411-9568-005056b45654&#8242;,TaskID=guid’FFAE5086-BE7A-E411-9568-005056B45654′)

When using PSI, we can access the required information via the TASK_REM_WORK and TASK_PCT_WORK_COMP fields in ProjectDataSet.TaskRow, that means, rows in the Task property (type of  ProjectDataSet.TaskDataTable) of the ProjectDataSet. The first row in the record set contains the information about the project summary task.

We could create our own extensions for the client object model (wrapping around just this piece of  PSI), as I illustrated for the managed, and for the ECMAScript object model as well, but it would require a lot of work, so I ignored this option for now. Instead of this, I’ve created a simple .NET console application utilizing the PSI (see the most important part of the code below). Unfortunately, I have not found a method that returns only a specific task of a specific project, so I had to call the ReadProjectEntities method to read all of the tasks of the project.

  1. _projectClient = new SvcProject.ProjectClient(ENDPOINT_PROJECT, pwaUrl + "/_vti_bin/PSI/ProjectServer.svc");
  2. _projectClient.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;
  3.  
  4. Guid projId = Guid.Parse("d0ae5086-be7a-e411-9568-005056b45654");
  5. int taskEntityId = 2;
  6.  
  7. var projEntitiesDS = _projectClient.ReadProjectEntities(projId, taskEntityId, SvcProject.DataStoreEnum.PublishedStore);
  8. var tasksTable = projEntitiesDS.Task;
  9.  
  10. foreach (SvcProject.ProjectDataSet.TaskRow task in tasksTable.Rows)
  11. {
  12.     Console.WriteLine(string.Format("TASK_OUTLINE_NUM: {0}; TASK_PCT_WORK_COMP: {1}; TASK_REM_WORK: {2}", task.TASK_OUTLINE_NUM, task.TASK_PCT_WORK_COMP, task.TASK_REM_WORK));
  13. }

I’ve captured the request and the response using Fiddler:

image

Then extended my JavaScript code with methods that assemble the request in the same format, submit it to the server, then parse the required fields out of the response.

First, I needed a helper method to format strings:

  1. String.format = (function () {
  2.     // The string containing the format items (e.g. "{0}")
  3.     // will and always has to be the first argument.
  4.     var result = arguments[0];
  5.  
  6.     // start with the second argument (i = 1)
  7.     for (var i = 1; i < arguments.length; i++) {
  8.         // "gm" = RegEx options for Global search (more than one instance)
  9.         // and for Multiline search
  10.         var regEx = new RegExp("\\{" + (i – 1) + "\\}", "gm");
  11.         result = result.replace(regEx, arguments[i]);
  12.     }
  13.  
  14.     return result;
  15. });

In my Angular controller I defined this function to format dates:

  1. $scope.formatDate = function (date) {
  2.     var formattedDate = '';
  3.     if ((typeof date != "undefined") && (date.year() > 1)) {
  4.         formattedDate = String.format("{0}.{1}.{2}", date.year(), date.month() + 1, date.date());
  5.     }
  6.  
  7.     return formattedDate;
  8. }

Next, in the controller we get the ID of the project for the current PWS, and we read project properties that are available via the client object model, and finally the ones, that are available only via PSI:

  1. var promiseWebProps = ProjService.getWebProps($scope);
  2. promiseWebProps.then(function (props) {
  3.     $scope.projectId = props.projectId;
  4.  
  5.     // read the project properties that are available via the client object model
  6.     var promiseProjProp = ProjService.getProjectProps($scope);
  7.     promiseProjProp.then(function (props) {
  8.         $scope.projStartDate = moment(props.projStartDate);
  9.         $scope.projFinishDate = moment(props.projFinishDate);
  10.         $scope.percentComp = props.percentComp;
  11.     }, function (errorMsg) {
  12.         console.log("Error: " + errorMsg);
  13.     });
  14.  
  15.     // read the project properties that are available only via PSI
  16.     var promiseProjPropEx = ProjService.getProjectPropsEx($scope);
  17.     promiseProjPropEx.then(function (propsEx) {
  18.         $scope.remainingWork = Math.round(propsEx.remainingWork / 600) / 100;
  19.         $scope.percentWorkComp = propsEx.percentWorkComp;
  20.     }, function (errorMsg) {
  21.         console.log("Error: " + errorMsg);
  22.     });
  23.  
  24. }, function (errorMsg) {
  25.     console.log("Error: " + errorMsg);
  26. });

As you can see, the value we receive in the remainingWork property should be divided by 600 and 100 to get the value in hours.

In our custom ProjService service I’ve implemented the corresponding methods.

The project ID is stored in the property bag of the PWS in a property called MSPWAPROJUID (see this post about how to read property bags from the client object model):

  1. this.getWebProps = function ($scope) {
  2.     var deferred = $q.defer();
  3.  
  4.     var ctx = SP.ClientContext.get_current();
  5.  
  6.     var web = ctx.get_web();
  7.     var props = web.get_allProperties();
  8.     ctx.load(props);
  9.  
  10.  
  11.     ctx.executeQueryAsync(
  12.         function () {
  13.             var allProps = props.get_fieldValues();
  14.  
  15.             deferred.resolve(
  16.                 {
  17.                     projectId: allProps.MSPWAPROJUID
  18.                 });
  19.         },
  20.         function (sender, args) {
  21.             deferred.reject('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
  22.         }
  23.     );
  24.  
  25.     return deferred.promise;
  26. };

Having the project ID, reading project properties via the client object model should be straightforward as well:

  1. this.getProjectProps = function ($scope) {
  2.     var deferred = $q.defer();
  3.  
  4.     var ctx = SP.ClientContext.get_current();
  5.  
  6.     var projContext = PS.ProjectContext.get_current();
  7.  
  8.     projContext.set_isPageUrl(ctx.get_isPageUrl);
  9.     var proj = projContext.get_projects().getById($scope.projectId);
  10.     projContext.load(proj, "StartDate", "FinishDate", "PercentComplete");
  11.  
  12.     projContext.executeQueryAsync(
  13.         function () {
  14.             deferred.resolve({
  15.                 projStartDate: proj.get_startDate(),
  16.                 projFinishDate: proj.get_finishDate(),
  17.                 percentComp: proj.get_percentComplete()
  18.             });
  19.         },
  20.         function (sender, args) {
  21.             deferred.reject('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
  22.         }
  23.     );
  24.  
  25.     return deferred.promise;
  26. };

Reading the ‘extra’ properties via PSI is a bit more complex. First, we assemble the request XML as we captured it with Fiddler when used the console application mentioned above, and post it to the server. Next, we process the response (see the code of the helper method buildXMLFromString farther below), and parse out the necessary properties from the project summary task (that is the Task node having rowOrder = 0) using XPath queries.

  1. this.getProjectPropsEx = function () {
  2.     var deferred = $q.defer();
  3.    
  4.     // assuming your PWA is located at /PWA
  5.     var psiUrl = String.format("{0}//{1}/PWA/_vti_bin/PSI/ProjectServer.svc", window.location.protocol, window.location.host);
  6.    
  7.     $http({
  8.         method: 'POST',
  9.         url: psiUrl,
  10.         data: String.format('<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/"><s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance&quot; xmlns:xsd="http://www.w3.org/2001/XMLSchema"><ReadProjectEntities xmlns="http://schemas.microsoft.com/office/project/server/webservices/Project/"><projectUid&gt;{0}</projectUid><ProjectEntityType>2</ProjectEntityType><dataStore>PublishedStore</dataStore></ReadProjectEntities></s:Body></s:Envelope>', $scope.projectId),
  11.         headers: {
  12.             "Content-Type": 'text/xml; charset=utf-8',
  13.             "SOAPAction": "http://schemas.microsoft.com/office/project/server/webservices/Project/ReadProjectEntities&quot;
  14.         }
  15.     }).success(function (data) {
  16.         var dataAsXml = buildXMLFromString(data);
  17.         dataAsXml.setProperty('SelectionLanguage', 'XPath');
  18.         dataAsXml.setProperty('SelectionNamespaces', 'xmlns:pds="http://schemas.microsoft.com/office/project/server/webservices/ProjectDataSet/&quot; xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"');
  19.         var projSumTaskNode = dataAsXml.selectSingleNode("//pds:Task[@msdata:rowOrder=0]");
  20.         var remainingWork = projSumTaskNode.selectSingleNode("pds:TASK_REM_WORK").nodeTypedValue;
  21.         var percentWorkComp = projSumTaskNode.selectSingleNode("pds:TASK_PCT_WORK_COMP").nodeTypedValue;
  22.         deferred.resolve(
  23.             {
  24.                 remainingWork: remainingWork,
  25.                 percentWorkComp: percentWorkComp
  26.             });
  27.     })
  28.     .error(function (data, status) {
  29.         deferred.reject('Request failed. ' + data);
  30.     });
  31.     
  32.     return deferred.promise;
  33. }

These are the helper methods I used for processing the response text as XML:

  1. function createMSXMLDocumentObject() {
  2.     if (typeof (ActiveXObject) != "undefined") {
  3.         // http://blogs.msdn.com/b/xmlteam/archive/2006/10/23/using-the-right-version-of-msxml-in-internet-explorer.aspx
  4.         var progIDs = [
  5.                         "Msxml2.DOMDocument.6.0",
  6.                         "Msxml2.DOMDocument.3.0",
  7.                         "MSXML.DOMDocument"
  8.         ];
  9.         for (var i = 0; i < progIDs.length; i++) {
  10.             try {
  11.                 return new ActiveXObject(progIDs[i]);
  12.             } catch (e) { };
  13.         }
  14.     }
  15.  
  16.     return null;
  17. }
  18.  
  19. function buildXMLFromString(text) {
  20.     var xmlDoc;
  21.  
  22.     xmlDoc = createMSXMLDocumentObject();
  23.     if (!xmlDoc) {
  24.         alert("Cannot create XMLDocument object");
  25.         return null;
  26.     }
  27.  
  28.     xmlDoc.loadXML(text);
  29.  
  30.     var errorMsg = null;
  31.     if (xmlDoc.parseError && xmlDoc.parseError.errorCode != 0) {
  32.         errorMsg = "XML Parsing Error: " + xmlDoc.parseError.reason
  33.                     + " at line " + xmlDoc.parseError.line
  34.                     + " at position " + xmlDoc.parseError.linepos;
  35.     }
  36.     else {
  37.         if (xmlDoc.documentElement) {
  38.             if (xmlDoc.documentElement.nodeName == "parsererror") {
  39.                 errorMsg = xmlDoc.documentElement.childNodes[0].nodeValue;
  40.             }
  41.         }
  42.         else {
  43.             errorMsg = "XML Parsing Error!";
  44.         }
  45.     }
  46.  
  47.     if (errorMsg) {
  48.         alert(errorMsg);
  49.         return null;
  50.     }
  51.  
  52.     return xmlDoc;
  53. }

Having an HTML template like this one:

  1. <div><span>% complete:</span><span>{{percentComp}}%</span></div>
  2. <div><span>% work complete:</span><span>{{percentWorkComp}}%</span></div>
  3. <div><span>Remaining work:</span><span>{{remainingWork}} Hours</span></div>
  4. <div><span>Project start:</span><span>{{formatDate(projStartDate)}}</span></div>
  5. <div><span>Project finish:</span><span>{{formatDate(projFinishDate)}}</span></div>

the result should be displayed similar to this one:

image

A drawback of this approach (not to mention the fact that it is pretty hacky) is, that due the ReadProjectEntities method, all of the fields of all of the project tasks should be downloaded to the client, although we need only a few fields of a single task, the project summary task. So it would make sense to implement some kind of  caching on the client side, but it is out of the scope of this post. But as long as Microsoft does not provide all the project fields in the client object model, I have not found any better solution that would require a relative small effort.

March 4, 2015

How to find out the real number of Queue Jobs

Filed under: PowerShell, PS 2013, PSI — Tags: , , — Peter Holpar @ 00:45

Recently we had an issue with Project Server. Although the Microsoft Project Server Queue Service was running, the items in the queue has not been processed, and the performance of the system degraded severely. The same time we found a lot of cache cluster failures in Windows Event Logs and ULS logs, it is not clear which one was the source of the problem and which is the result of the other. The “solution” was to install the February 2015 CU SharePoint Product Updates and restarting the server. 

However, even after the restart the number of the job entries seemed to be constant when checking via the PWA Settings / Manage Queue Jobs (Queue and Database Administration): at the first page load the total number displayed at the left bottom of the grid was 1000, however when we paged through the results or refreshed the status, the total was changed to 500 (seems to be an issue with the product). It means that PWA administrators don’t see the real number of the entries.

But how could one then get the real number of the queue jobs?

If you have permission to access the performance counters on the server (in the simplest case, if you are a local admin), then you can use the Current Unprocessed Jobs counter (ProjectServer:QueueGeneral), that – as its name suggests – give the total number of the current unprocessed jobs.

You have to find an alternative solution if you need the count of jobs having other status, or need even more granulate results, for example, the number of job entries that are ready for processing and are related to publishing a project.

The QueueJobs property of the Project class in the client object model (see the QueueJob and QueueJobCollection classes as well) provides only information related to a given project, and the same is true for the REST interface, where you can access the same information for your project like (the Guid is the ID of your project):

http://YourProjServer/PWA/_api/ProjectServer/Projects(‘98138ffd-d0fa-e311-83c6-005056b45654&#8217;)/QueueJobs

The best solution I’ve found is based on the GetJobCount method in the QueueSystem object in the PSI interface. Let’s see some practical PowerShell examples how to use it.

To get the reference for the proxy:

$pwaUrl = "http://YourProjServer/PWA&quot;
$svcPSProxy = New-WebServiceProxy -Uri ($pwaUrl + "/_vti_bin/PSI/QueueSystem.asmx?wsdl") –UseDefaultCredential

To get the number of all entries without filtering:

$svcPSProxy.GetJobCount($Null, $Null, $Null)

For filtering, we can use the second and the third parameter of the method: the jobStates and messageTypes that are arrays of the corresponding JobState and QueueMsgType enumerations. Both of these enums are available as nested enums in the Microsoft.Office.Project.Server.Library.QueueConstants class. This class is defined in the Microsoft.Office.Project.Server.Library assembly, so if we would like to use the enums, we should load the assembly first:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Project.Server.Library")

Note: you can use the integer values corresponding to the enum values like:

$jobStates = (1, 7)

…however I don’t find it very developer friendly to use such magical constants in code, and you lose the autocomplete feature of PowerShell as well that you have when working with the enums as displayed below:

$jobStates = (
  [int][Microsoft.Office.Project.Server.Library.QueueConstants+JobState]::ReadyForProcessing,
  [int][Microsoft.Office.Project.Server.Library.QueueConstants+JobState]::ProcessingDeferred
)

similarly for message types:

$msgTypes = (
  [int][Microsoft.Office.Project.Server.Library.QueueConstants+QueueMsgType]::ReportingProjectPublish,
  [int][Microsoft.Office.Project.Server.Library.QueueConstants+QueueMsgType]::ReportingProjectDelete
)

You can then access the count of filtered items like:

$svcPSProxy.GetJobCount($Null, $jobStates, $Null)

or

$svcPSProxy.GetJobCount($Null, $jobStates, $msgTypes)

It is worth to know that the QueueConstants class has two methods (PendingJobStates and CompletedJobStates) that return a predefined set of the enum values as a generic IEnumreable<QueueConstants.JobState>. We can use these methods from PowerShell as well:

$jobStates = Microsoft.Office.Project.Server.Library.QueueConstants]::PendingJobStates() | % { [int]$_ }

or

$jobStates = Microsoft.Office.Project.Server.Library.QueueConstants]::CompletedJobStates() | % { [int]$_ }

September 29, 2014

Importing multi-level Lookup Tables using PowerShell

Filed under: ALM, PowerShell, Project Server, PSI — Tags: , , , — Peter Holpar @ 21:54

Recently I’m working quite a lot with Project Server 2013. My tasks include – beyond development – creation of methods that supports the continuous delivery of the results from the development environment to the test and production environments. I found that my old friend, PowerShell is an invaluable tool in this field as well.

Recently I had to solve a problem, where we had a rather complex, multi-level lookup table (RBS) on the development server, and we had to transfer the same structure on each deployment to the test server. Typing the same structure via the UI each time would have been a very boring and time consuming activity.

If we export the structure via the UI to Excel,

image

the result looks like this:

image

However, when we try to paste the values to the lookup list via the UI, the fields are shifted to the right: the values in the Level field become to the values of the Value field, the Value becomes to the Description, and the original Description is lost, making the whole pasting worthless.

image

I found a very useful PowerShell script on the blog of Paul Mather (the code is available in the TechNet Script Center as well). This script utilizes the PSI interface, however is limited to a single level of values, no hierarchical lookup tables.

I’ve extended the sample using the generic Stack object of the .NET Framework, pushing and popping the Guids of the parent items, and importing the value of the Description field as well. Otherwise most of the code was borrowed from, and the functionality is identical to the original version of Paul. As input file, a TAB separated text file is used without field name headers, including the Level, Value and Description fields, in the case above, for example:

1    Value 1    Desc 1
2    Value 1_1    Desc 1.1
3    Value 1_1_1    Desc 1.1.1
2    Value 1_2    Desc 1.2
3    Value 1_2_1    Desc 1.2.1
2    Value 1_3    Desc 1.3

This sample is limited to lookup tables with character-based code sequences.

The PowerShell script that enables the mulit-level import:

  1. #Get lookup table values to add
  2. $values = Get-Content "C:\Data\PowerShell\RBSValues.txt"
  3.  
  4. #Specify Lookup table to update
  5. $lookupTableName = "RBS"
  6. $lcid = 1033
  7. $emptyString = [String]::empty
  8. $svcPSProxy = New-WebServiceProxy -Uri "http://sp2013/pwa/_vti_bin/PSI/LookupTable.asmx?wsdl&quot; -UseDefaultCredential
  9. $lookupTableGuid = ($svcPSProxy.ReadLookupTables($emptyString, 0, $lcid).LookupTables  | ? {$_.LT_NAME -eq $lookupTableName }).LT_UID
  10. $lookupTable = $svcPSProxy.ReadLookupTablesbyUids($lookupTableGuid, 1, $lcid)
  11. #get lookup table count
  12. $lookuptableValues = $svcPSProxy.ReadLookupTablesbyUids($lookupTableGuid, 0, $lcid).LookupTableTrees
  13. $count = $lookuptableValues.Count + 1
  14. #update lookup table…
  15. $stack = New-Object System.Collections.Generic.Stack[Guid]
  16. $lastLevel = 1
  17.  
  18. $values | % {
  19.     $fields = $_ -split '\t+'
  20.         $level = $fields[0]
  21.         $text = $fields[1]
  22.         $desc = $fields[2]
  23.  
  24.     $guid = [Guid]::NewGuid()
  25.     # Write-Host Count: $count, text: $text, Guid: $guid, Level: $level, Last level: $lastLevel
  26.     $parentGuid = $lastGuid
  27.     If ($lastLevel -lt $level) {
  28.         $stack.Push($lastGuid)
  29.         # Write-Host Parent GUID Pushed: $parentGuid
  30.     }
  31.     Else {
  32.         While (($stack.Count -ge ($level)) -and ($stack.Count -gt 1)) {
  33.             # Write-Host Popping level ($stack.Count + 1)
  34.             $parentGuid = $stack.Pop()
  35.             # Write-Host Parent GUID Popped: $parentGuid
  36.         }
  37.         If ($stack.Count -gt 0) {
  38.             $parentGuid = $stack.Peek()
  39.             # Write-Host Parent GUID Peeked: $parentGuid
  40.         }
  41.     }
  42.  
  43.  
  44.     $LookupRow = $lookuptable.LookupTableTrees.NewLookupTableTreesRow()
  45.     If (-Not [String]::IsNullOrEmpty($desc)) {
  46.         $LookupRow.LT_VALUE_DESC = $desc
  47.     }
  48.     $LookupRow.LT_STRUCT_UID = $guid
  49.     $LookupRow.LT_UID = $lookupTableGuid
  50.     $LookupRow.LT_VALUE_TEXT = $text
  51.     If ($level -gt 1) {
  52.         # Write-Host Parent GUID set: $parentGuid
  53.         $LookupRow.LT_PARENT_STRUCT_UID = $parentGuid
  54.     }
  55.     $LookupRow.LT_VALUE_SORT_INDEX =  ($count++)
  56.     $lookuptable.LookupTableTrees.AddLookupTableTreesRow($LookupRow)
  57.  
  58.     $lastGuid = $guid
  59.     $lastLevel = $level
  60. }
  61.  
  62. $Error.Clear()
  63. Try
  64.     {
  65.         $svcPSProxy.UpdateLookupTables($lookuptable , 0 , 1 , $lcid)
  66.     }
  67. Catch
  68.     {
  69.         Write-Host "Error updating the Lookup table, see the error below:" -ForeGroundColor Red -BackGroundColor White
  70.         Write-Host "$error" -ForeGroundColor Red
  71.     }
  72. If ($Error.Count -eq 0)
  73.     {
  74.         Write-Host "The lookup table $lookupTablename has been updated with the values from the text file specified" -ForeGroundColor Green
  75.     }
  76. Else
  77.     {
  78.         Write-Host "The lookup table $lookupTablename has not been updated with the values from the text file specified, please see error" -ForeGroundColor Red -BackGroundColor White
  79.     }
  80. #force checkin in case of failure
  81. $Error.Clear()
  82. Try
  83.     {
  84.      $svcPSProxy.CheckInLookUpTables($lookupTableGuid, 1)
  85.     }
  86. Catch
  87.     {
  88.         If ($error -match "LastError=CICONotCheckedOut")
  89.             {
  90.     
  91.             }
  92.         Else
  93.         {
  94.             Write-Host "Error checking the Lookup table, see the error below:" -ForeGroundColor Red -BackGroundColor White
  95.             Write-Host "$error" -ForeGroundColor Red
  96.         }
  97.     }

The script includes a lot of Write-Host cmdlets to enable tracking of the process. These are commented in the version above. You are free to either use or delete these lines as you wish.

Note: Don’t forget to alter the file path, the URI and the lookup table name, and the LCID as well, if you are working with a non-English version of PWA.

Blog at WordPress.com.