Second Life of a Hungarian SharePoint Geek

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"
$svcPSProxy = New-WebServiceProxy -Namespace PSIProxy -Uri ($pwaUrl + "/_vti_bin/PSI/View.asmx?wsdl") -UseDefaultCredential
$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

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

To remove a field from a view:

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

To delete the view itself:


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.WTABLE_UID = $fieldToAppend.WTABLE_UID
$newField.WFIELD_NAME_SQL = $fieldToAppend.WFIELD_NAME_SQL
$newField.WVIEW_UID = $view.ViewReports.WVIEW_UID
$newField.WVIEW_FIELD_ORDER = $maxFieldOrder + 1
$newField.WVIEW_FIELD_WIDTH = 100
$newField.WVIEW_FIELD_CUSTOM_LABEL = [System.DBNull]::Value


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.WTABLE_UID = $fieldToInject.WTABLE_UID
$newField.WFIELD_NAME_SQL = $fieldToInject.WFIELD_NAME_SQL
$newField.WVIEW_UID = $view.ViewReports.WVIEW_UID
$newField.WVIEW_FIELD_ORDER = $fieldInjectBeforeOrder
$newField.WVIEW_FIELD_WIDTH = 100
$newField.WVIEW_FIELD_CUSTOM_LABEL = [System.DBNull]::Value


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_NAME_SQL = $fieldToAppend.WFIELD_NAME_SQL
$newField.WVIEW_UID = $newView.WVIEW_UID
$newField.WVIEW_FIELD_WIDTH = 100
$newField.WVIEW_FIELD_CUSTOM_LABEL = [System.DBNull]::Value

$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

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


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:


$pwaUrl = "http://YourProjectServer/pwa"

# create shortcuts
$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."

$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.

April 8, 2015

Automating the Deployment of a Customized Project Web Site Template via PowerShell and the Managed Client Object Model

Filed under: ALM, Managed Client OM, PowerShell, Project Server — Tags: , , , — Peter Holpar @ 21:45

Assume, you have created a customized web site template for your enterprise project type in the development environment as described here, and now you would like to deploy it into the test farm. Of course, you can manually delete the former site template, upload the new one, re-configure it to be the associated web site template for your enterprise project type, and finally re-create your test project (that means, checking in and deleting the existing one, and create it again using the new template), but this procedure is boring, cumbersome and – as any human-based process – rather error-prone.

Why do not automate this step as well?

I’ve created a PowerShell script that performs the steps outlined above. The first steps (deleting the former version of the site template and uploading the new one) can be done by native PowerShell Cmdlets, but for the remaining, Project Server related tasks require the Managed Client Object Model, so we import the necessary assemblies into the process.

First we get a list of all projects and a list of all enterprise project types, then query for the right ones on the “client side”.

Note: Although PowerShell does not support .NET extension methods (like the Where and Include methods of the client object model) natively, we could restrict the items returned by these queries to include really only the item we need (see solution here), and include only the properties we need (as described here). As the item count of the projects and enterprise project types is not significant, and we should use the script on the server itself due to the SharePoint Cmdlets, it has no sense in this case to limit the network traffic via these tricks.

Next, we update the web site template setting (WorkspaceTemplateName  property) of the enterprise project type. We need this step as the original vale was reset to the default value as we deleted the original site template on re-upload.

If the test project is found, we delete it (after we checked it in, if it was checked out), and create it using the updated template.

Since these last steps (project check-in, deletion, and creation) are all queue-based operations, we should use the WaitForQueue method to be sure the former operation is completed before we start the next step.

$pwaUrl = "http://YourProjectServer/PWA/"
$solutionName = "YourSiteTemplate"
$wspFileName = $solutionName + ".wsp"
$timeoutSeconds = 1000
$projName = "TestProj"

# English
$projType = "Enterprise Project"
$pwaLcid = 1033
# German
#$projType = "Enterprise-Projekt"
#$pwaLcid = 1031

# path of the folder containing the .wsp
$localRootPath = "D:\SiteTemplates\"
$wspLocalPath = $localRootPath + $wspFileName

# uninstall / remove the site template if activated / found
$solution = Get-SPUserSolution -Identity $wspFileName -Site $pwaUrl -ErrorAction SilentlyContinue
If ($solution -ne $Null) {
  If ($solution.Status -eq "Activated") {
    Write-Host Uninstalling web site template
    Uninstall-SPUserSolution -Identity $solutionName -Site $pwaUrl -Confirm:$False
  Write-Host Removing web site template
  Remove-SPUserSolution -Identity $wspFileName -Site $pwaUrl -Confirm:$False

# upload and activate the new version
Write-Host Uploading new web site template
Add-SPUserSolution -LiteralPath $wspLocalPath -Site $pwaUrl
Write-Host Installing new web site template
$dummy = Install-SPUserSolution -Identity $solutionName -Site $pwaUrl
# set the path according the location of the assemblies
Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.ProjectServer.Client.dll"
Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

$projectContext = New-Object Microsoft.ProjectServer.Client.ProjectContext($pwaUrl)

# get lists of enterprise project types and projects
$projectTypes = $projectContext.LoadQuery($projectContext.EnterpriseProjectTypes)
$projects = $projectContext.Projects
$projectList = $projectContext.LoadQuery($projectContext.Projects)


$entProjType = $projectTypes | ? { $_.Name -eq $projType }
$project = $projectList | ? { $_.Name -eq $projName }

Write-Host Updating web site template for the enterprise project type
$web = Get-SPWeb $pwaUrl
$template = $web.GetAvailableWebTemplates($pwaLcid) | ? { $_.Title -eq $solutionName }

$entProjType.WorkspaceTemplateName = $template.Name

If ($project -ne $Null) {
  If ($project.IsCheckedOut) {
    Write-Host Project $projName is checked out, checking it in before deletion
    $checkInJob = $project.Draft.CheckIn($True)
    $checkInJobState = $projectContext.WaitForQueue($checkInJob, $timeoutSeconds)
    Write-Host Check-in project job status: $checkInJobState
  Write-Host Deleting existing project $projName
  # we can delete the project either this way
  #$removeProjResult = $projects.Remove($project)
  #$removeJob = $projects.Update()
  # or
  $removeJob = $project.DeleteObject()
  $removeJobState = $projectContext.WaitForQueue($removeJob, $timeoutSeconds)
  Write-Host Remove project job status: $removeJobState

I found the set of Project Server PowerShell Cmdlets is limited, and rather operation-based. You can use it, as long as your single task is to administer Project Server instances and databases. However, when it comes to the interaction with Project Server entities, you have to involve the Managed Client Object Model. Hopefully this example provides not only a reusable tool, but also helps you understand, how to extend your own PowerShell library with the methods borrowed from the client side .NET libraries.

Automating Project Server development tasks via PowerShell and the Client Object Model – Customizing Project Web Site templates

Filed under: ALM, Managed Client OM, PowerShell, Project Server — Tags: , , , — Peter Holpar @ 21:35

I start with a note this time: Even though you were not interested in Project Server itself at all, I suggest you to read the post further, while most of the issues discussed below are not Project Server specific, they apply to SharePoint as well.

Recently I work mostly on a Project Server customization project. As I’ve learned on my former development projects, I try to automate so much repetitive tasks as possible (like automating the PWA provisioning), thus remains more time for the really interesting stuff. I plan to post my results on this blog to share the scripts and document the experiences for myself as well.

One of the very first tasks (and probably a never-ending one) was to create a customized Project Web Site (PWS) site template. New Enterprise Projects created in the Project Web Access (PWA) should have their PWS created based on the custom site template.

The process of customizing a PWS site template is described in this post, however, there are a few issues if we apply this approach alone, just to name a few of them:

– PWS master pages cannot be edited using SharePoint Designer by default. There is a workaround for this issue.

– If I create a custom master page for the PWA and would like a PWS to refer the same master page, I can set it for example using PowerShell. However, if I create a site template from this PWS, this configuration seems to be lost in the template, and template refers to the default seattle.master. I have similar experience with the site image / logo, I can set one, but this setting seems to be not saved in the template.

– The standard navigation structure of a project site (and all site template created based on it) contains project-specific navigation nodes, like Project Details that contains the Guid of the current project as a query string parameter. If you create a site template from this site, any project sites that will be created based on this template will contain this node twice: one of the is created based on the site template (wrong Guid, referring to the project the original site belongs to, thus wrong URL), and another one is created dynamically as the project web site gets provisioned.

The workflow of my web site template creation and customization process includes four main parts, and two of them – step 2 and step 4 – are automated by our script.

The first part of the process (including step 1 and step 2) is optional. If you have changed nothing in your web site prototype, you can immediately start with the manual manipulation of the extracted web site template content (step 3), otherwise, we have to get a fresh version of the template into our local system for the further customizations.

Step 1: Creation and customization a SharePoint web site, that serves as a prototype for the web site template.

A SharePoint web site is customized based on the requirements using the SharePoint web UI, SharePoint Designer (for PWA see this post), or via other tools, like PowerShell scripts (for example, JSLink settings). This is a “manual” task.

Step 2: Creation of the web site template based on the prototype, downloading and extracting the site template.

A site template is created (including content) based on the customized web site. If a former site template having the same name already exists, if will be deleted first.

The site template is downloaded to the local file system (former file having the same name is deleted first).

The content of the .wsp file (CAB format) is extracted into a local folder (folder having the same name is deleted first, if it exists).

Step 3: Customization of the extracted web site template artifacts.

The script is paused. In this step you have the chance to manual customization of the solution files, like ONet.xml.

Step 4: Compressing the customized files into a new site template, and uploading it to SharePoint.

After a key press the script runs further.

Files having the same name as our site template and extension of .cab or .wsp will be deleted. The content of the folder is compressed as .cab and the renamed to .wsp.

In the final step the original web site template is removed and the new version is installed.

Next, a few words about the CAB extraction and compression tools I chose for the automation. Minimal requirements were that the tool must have a command line interface and it should recognize the folder structure to be compressed automatically, without any helper files (like the DDF directive file in case of makecab).

After reading a few comparisons (like this and this one) about the alternative options, I first found IZArc and its command line add-on (including IZARCC for compression and IZARCE for extraction, see their user’s manual for details) to be the best choice. However after a short test I experienced issues with the depth of the folder path and file name length in case of IZARCE, so I fell back to extrac32 for the extraction.

Finally, the script itself:

$pwaUrl = "http://YourProjectServer/PWA/"
$pwsSiteTemplateSourceUrl = $pwaUrl + "YourPrototypeWeb"
$solutionName = "YourSiteTemplate"
$wspFileName = $solutionName + ".wsp"
$cabFileName = $solutionName + ".cab"
$siteTemplateTitle = $solutionName
$siteTemplateName = $solutionName
$siteTemplateDescription = "PWS Website Template"

$localRootPath = "D:\SiteTemplates\"
$wspExtractFolderName = $solutionName
$wspExtractFolder = $localRootPath + $wspExtractFolderName
$wspFilePath = $localRootPath + $wspFileName
$wspLocalPath = $localRootPath + $wspFileName
$wspUrl = $pwaUrl + "_catalogs/solutions/" + $wspFileName

$cabFilePath = $localRootPath + $cabFileName

function Using-Culture (
   [System.Globalization.CultureInfo]   $culture = (throw "USAGE: Using-Culture -Culture culture -Script {…}"),
   $script = (throw "USAGE: Using-Culture -Culture culture -Script {…}"))
     $OldCulture = [Threading.Thread]::CurrentThread.CurrentCulture
     $OldUICulture = [Threading.Thread]::CurrentThread.CurrentUICulture
         try {
                 [Threading.Thread]::CurrentThread.CurrentCulture = $culture
                 [Threading.Thread]::CurrentThread.CurrentUICulture = $culture
                 Invoke-Command $script
         finally {
                 [Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture
                 [Threading.Thread]::CurrentThread.CurrentUICulture = $OldUICulture

function Remove-SiteTemplate-IfExists($solutionName, $wspFileName, $pwaUrl) 
  $us = Get-SPUserSolution -Identity $solutionName -Site $pwaUrl -ErrorAction SilentlyContinue
  if ($us -ne $Null)
    Write-Host Former version of site template found on the server. It will be removed…
    Uninstall-SPUserSolution -Identity $solutionName -Site $pwaUrl -Confirm:$False
    Remove-SPUserSolution -Identity $wspFileName -Site $pwaUrl -Confirm:$False

function Remove-File-IfExists($path)
  If (Test-Path $path)
    If (Test-Path $path -PathType Container)
      Write-Host Deleting folder: $path
      Remove-Item $path -Force -Recurse
      Write-Host Deleting file: $path
      Remove-Item $path -Force

Do { $downloadNewTemplate = Read-Host "Would you like to get a new local version of the site template to edit? (y/n)" }
Until ("y","n" -contains $downloadNewTemplate )

If ($downloadNewTemplate -eq "y")

    Remove-SiteTemplate-IfExists $solutionName $wspFileName $pwaUrl

    Using-Culture de-DE { 
     Write-Host Saving site as site template including content
     $web = Get-SPWeb $pwsSiteTemplateSourceUrl
     $web.SaveAsTemplate($siteTemplateName, $siteTemplateTitle, $siteTemplateDescription, 1)

  Remove-File-IfExists $cabFilePath

  Write-Host Downloading site template
  $webClient = New-Object System.Net.WebClient
  $webClient.UseDefaultCredentials  = $True 
  $webClient.DownloadFile($wspUrl, $cabFilePath)

  # clean up former version before downloading the new one
  # be sure you do not lock the deletion, for example, by having one of the subfolders opened in File Explorer,
  # or via any file opened in an application
  Remove-File-IfExists $wspExtractFolder

  Write-Host Extracting site template into folder $wspExtractFolder
  # limited file lenght / folder structure depth! 😦
  #& "C:\Program Files (x86)\IZArc\IZARCE.exe" -d $cabFilePath $wspExtractFolder

  #expand $cabFilePath $wspExtractFolder -F:*.*
  extrac32 /Y /E $cabFilePath /L $wspExtractFolder

Write-Host "Alter the extracted content of the site template, then press any key to upload the template…"
# wait any key press without any output to the console
$dummy = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")

# clean up former version before creating the new one
# TODO rename it using a date time pattern instead of deletion!
Remove-File-IfExists $cabFilePath
Remove-File-IfExists $wspFilePath

# makecab: we cannot include multiple files directly. To do that, we have to create a directive file called a Diamond Directive File(DDF) and include instructions in it
& "C:\Program Files (x86)\IZArc\IZARCC.exe" -a -r -p $cabFilePath $wspExtractFolder

Rename-Item $cabFilePath $wspFileName

# remove former solution before uploading and activating the new one
Remove-SiteTemplate-IfExists $solutionName $wspFileName $pwaUrl

Write-Host Installing the new version of the site template
Add-SPUserSolution -LiteralPath $wspFilePath -Site $pwaUrl
$dummy = Install-SPUserSolution -Identity $solutionName -Site $pwaUrl

Note: If you are working with the English version of the PWA and have an English operating system on the server, you don’t need the Using-Culture function. To learn more about it see this post.

March 10, 2015

Automating the Provisioning of a PWA-Instance

Filed under: ALM, PowerShell, PS 2013 — Tags: , , — Peter Holpar @ 23:56

When testing our custom Project Server 2013 solutions in the development system, or deploying them to the test system I found it useful to be able to use a clean environment (new PWA instance having an empty project database and separate SharePoint content database for the project web access itself and the project web sites) each time.

We wrote a simple PowerShell script that provisions a new PWA instance, including:
– A separate SharePoint content database that should contain only a single site collection: the one for the PWA. If the content DB already exists, we will use the existing one, otherwise we create a new one.
– The managed path for the PWA.
– A new site collection for the PWA using the project web application site template, and the right locale ID (1033 in our case). If the site already exists (in case we re-use a former content DB), it will be dropped before creating the new one.
– A new project database. If a project database with the same name already exists on the SQL server, it will be dropped and re-created.
– The content database will be mounted to the PWA instance, and the admin permissions are set.

Note, that we have a prefix (like DEV or TEST) that identifies the system. We set the URL of the PWA and the database names using this prefix. The database server names (one for the SharePoint content DBs and another one for service application DBs) include the prefix as well, and are configured via aliases in the SQL Server Client Network Utilities, making it easier to relocate the databases if needed.

$environmentPrefix = "DEV"

$webAppUrl = [string]::Format("http://ps-{0}", $environmentPrefix)

$contentDBName = [string]::Format("PS_{0}_Content_PWA", $environmentPrefix)
$contentDBServer = [string]::Format("PS_{0}_Content", $environmentPrefix)

$pwaMgdPathPostFix = "PWA"
$pwaUrl = [string]::Format("{0}/{1}", $webAppUrl, $pwaMgdPathPostFix)
$pwaTitle = "PWA Site"
$pwaSiteTemplate = "PWA#0"
$pwaLcid = 1033
$ownerAlias = "domain\user1"
$secondaryOwnerAlias = "domain\user2"

$projServDBName = [string]::Format("PS_{0}_PS", $environmentPrefix)
$projServDBServer = [string]::Format("PS_{0}_ServiceApp", $environmentPrefix)

Write-Host Getting web application at $webAppUrl
$webApp = Get-SPWebApplication -Identity $webAppUrl

$contentDatabase = Get-SPContentDatabase -Identity $contentDBName -ErrorAction SilentlyContinue

if ($contentDatabase -eq $null) {
  Write-Host Creating content database: $contentDBName
  $contentDatabase = New-SPContentDatabase -Name $contentDBName -WebApplication $webApp -MaxSiteCount 1 -WarningSiteCount 0 -DatabaseServer $contentDBServer
else {
  Write-Host Using existing content database: $contentDBName

$pwaMgdPath = Get-SPManagedPath -Identity $pwaMgdPathPostFix -WebApplication $webApp -ErrorAction SilentlyContinue
if ($pwaMgdPath -eq $null) {
  Write-Host Creating managed path: $pwaMgdPathPostFix
  $pwaMgdPath = New-SPManagedPath -RelativeURL $pwaMgdPathPostFix -WebApplication $webApp -Explicit
else {
  Write-Host Using existing managed path: $pwaMgdPathPostFix

$pwaSite = Get-SPSite –Identity $pwaUrl -ErrorAction SilentlyContinue
if ($pwaSite -ne $null) {
  Write-Host Deleting existing PWA site at $pwaUrl

Write-Host Creating PWA site at $pwaUrl
$pwaSite = New-SPSite –Url $pwaUrl –OwnerAlias $ownerAlias –SecondaryOwnerAlias  $secondaryOwnerAlias -ContentDatabase $contentDatabase –Template $pwaSiteTemplate -Language $pwaLcid –Name $pwaTitle

$projDBState = Get-SPProjectDatabaseState -Name $projServDBName -DatabaseServer $projServDBServer
if ($projDBState.Exists) {
  Write-Host Removing existing Project DB $projServDBName
  Remove-SPProjectDatabase –Name $projServDBName -DatabaseServer $projServDBServer -WebApplication $webApp
Write-Host Creating Project DB $projServDBName
New-SPProjectDatabase –Name $projServDBName -DatabaseServer $projServDBServer -Lcid $pwaLcid -WebApplication $webApp

Write-Host Bind Project Service DB to PWA Site
Mount-SPProjectWebInstance –DatabaseName $projServDBName -DatabaseServer $projServDBServer –SiteCollection $pwaSite

#Setting admin permissions on PWA
Grant-SPProjectAdministratorAccess –Url $pwaUrl –UserAccount $ownerAlias

Using this script helps us to avoid a lot of manual configuration steps, saves us a lot of time and makes the result more reproducible.

November 23, 2014

Managing Project Server Enterprise Custom Fields via the Managed Client Object Model

Filed under: ALM, Managed Client OM, Project Server — Tags: , , — Peter Holpar @ 07:39

In the previous post I described, how to manage Project Server lookup tables via the managed client object model. In this current post I  provide you some code snippets that help to manage enterprise custom fields.

As I wrote in a former post, we can deploy Project Server entities declaratively, via SharePoint solution packages (WSP), see that post for the details. An example for enterprise custom field (with and without referencing lookup tables) is included below. In the first part we define a lookup table we refer to later, see the second part for the field definitions:

  1. <?xml version="1.0" encoding="utf-8" ?>
  2. <ProjectServerEntities xmlns="">
  3.   <LookupTables>
  4.     <LookupTable Id="E7397277-1AB0-4096-B2DD-57029A055BA4" Name="YourLookupTable">
  5.       <SortOrder>UserDefined</SortOrder>
  6.       <Mask>
  7.         <MaskLength>0</MaskLength>
  8.         <MaskType>CHARACTERS</MaskType>
  9.         <MaskSeparator>.</MaskSeparator>
  10.       </Mask>
  11.       <LookupEntry>
  12.         <Id>8D3CFDD8-566B-4FA4-8C01-E315BCF13E74</Id>
  13.         <ParentId />
  14.         <SortIndex>86</SortIndex>
  15.         <Value>
  16.           <TextValue>Value1</TextValue>
  17.         </Value>
  18.       </LookupEntry>
  19.       <LookupEntry>
  20.         <Id>8A913280-C8D5-4E85-AE73-526EF5ABC686</Id>
  21.         <ParentId />
  22.         <SortIndex>100</SortIndex>
  23.         <Value>
  24.           <TextValue>Value2</TextValue>
  25.         </Value>
  26.       </LookupEntry>
  27.       <LookupEntry>
  28.         <Id>C89A98F0-795A-49EC-9031-BA892CE0D610</Id>
  29.         <ParentId />
  30.         <SortIndex>114</SortIndex>
  31.         <Value>
  32.           <TextValue>Value3</TextValue>
  33.         </Value>
  34.       </LookupEntry>
  35.     </LookupTable>
  36.   </LookupTables>
  38.   <CustomFields>
  39.     <CustomField Name="CustomProjectFieldTextWithLookupValue" Id="7574B64B-F230-4F38-ACB6-1C8E4E3D96DD">
  40.       <FieldType>TEXT</FieldType>
  41.       <EntityType>TaskEntity</EntityType>
  42.       <LookupTableUid>E7397277-1AB0-4096-B2DD-57029A055BA4</LookupTableUid>
  43.       <LookupAllowMultiSelect>false</LookupAllowMultiSelect>
  44.       <LookupDefaultValue></LookupDefaultValue>
  45.       <IsRequired>false</IsRequired>
  46.       <IsWorkflowControlled>false</IsWorkflowControlled>
  47.       <IsMultilineText>false</IsMultilineText>
  48.     </CustomField>
  49.     <CustomField Name="CustomTaskFieldFlag" Id="3ED6B19F-BB55-46B6-B3E0-08E68F56110E">
  50.       <FieldType>FLAG</FieldType>
  51.       <EntityType>ProjectEntity</EntityType>
  52.       <LookupTableUid></LookupTableUid>
  53.       <LookupAllowMultiSelect>false</LookupAllowMultiSelect>
  54.       <LookupDefaultValue></LookupDefaultValue>
  55.       <IsRequired>false</IsRequired>
  56.       <IsWorkflowControlled>false</IsWorkflowControlled>
  57.       <IsMultilineText>false</IsMultilineText>
  58.     </CustomField>
  60.   </CustomFields>
  61. </ProjectServerEntities>

In the first code snippet we simply dump out some of the most important information of our enterprise custom fields.

  1. using (var projectContext = new ProjectContext(pwaUrl))
  2. {
  3.     projectContext.Load(projectContext.CustomFields,
  4.         cfs => cfs.Include(
  5.             cf => cf.Name,
  6.             cf => cf.Id,
  7.             cf => cf.FieldType,
  8.             cf => cf.LookupTable.Name));
  9.     projectContext.ExecuteQuery();
  11.     projectContext.CustomFields.ToList().ForEach(cf =>
  12.     {
  13.         Console.WriteLine("Name [{0}], Id [{1}], FieldType [{2}], LookupTable [{3}]",
  14.             cf.Name, cf.Id, cf.FieldType, (cf.LookupTable.ServerObjectIsNull == true) ? "none" : cf.LookupTable.Name);
  15.     });
  16. }

Note the condition we use to decide if there is a lookup table associated with this field:

cf.LookupTable.ServerObjectIsNull == true

Personally, I don’t like code where one compares a logical variable to true or false instead of using that variable as the condition itself, but the type of the ServerObjectIsNull property is nullable (bool?), and I found a direct comparison simpler as

(cf.LookupTable.ServerObjectIsNull.HasValue) && (cf.LookupTable.ServerObjectIsNull.Value)

Alternatively, you can use the IsPropertyAvailable method for the condition (it returns a bool not bool?) as shown below:


Note the exclamation mark at the beginning, as we check the opposite as in the case of ServerObjectIsNull property (property is available with IsPropertyAvailable vs. not available in case of ServerObjectIsNull).

The next sample shows how to create a flag-type enterprise custom field for the task entity:

  1. using (var projectContext = new ProjectContext(pwaUrl))
  2. {
  3.     var fieldInfo = new CustomFieldCreationInformation
  4.     {
  5.         Id = Guid.NewGuid(),
  6.         Name = "FlagFieldName",
  7.         EntityType = projectContext.EntityTypes.TaskEntity,
  8.         FieldType = CustomFieldType.FLAG,
  9.     };
  11.     projectContext.CustomFields.Add(fieldInfo);
  12.     projectContext.CustomFields.Update();
  14.     projectContext.ExecuteQuery();
  15. }

You can simply assign a lookup table as well, as illustrated below, where we create a text-type enterprise custom field for the project entity:

  1. using (var projectContext = new ProjectContext(pwaUrl))
  2. {
  3.     projectContext.Load(projectContext.LookupTables, lts => lts.Include(lt => lt.Name));
  4.     projectContext.ExecuteQuery();
  6.     var lookupTable = projectContext.LookupTables.First(lt => lt.Name == "LookupTableName");
  8.     var fieldInfo = new CustomFieldCreationInformation
  9.     {
  10.         Id = Guid.NewGuid(),
  11.         Name = "TextFieldName",
  12.         EntityType = projectContext.EntityTypes.ProjectEntity,
  13.         FieldType = CustomFieldType.TEXT,
  14.         LookupTable = lookupTable
  15.     };
  17.     projectContext.CustomFields.Add(fieldInfo);
  18.     projectContext.CustomFields.Update();
  20.     projectContext.ExecuteQuery();
  21. }

Note, that we request a list of all lookup tables in a separate batch in this case, the select the right lookup table by name. Of course, if you know the ID (uid or objectId) of your lookup table, you don’t need that extra request, as you can use either

var uidOfTheLookupTable = new Guid("54338ffd-d0fa-e311-83c6-0050a3245643");
var lookupTable = projectContext.LookupTables.GetByGuid(uidOfTheLookupTable);


var objectIdOfTheLookupTable = "98238ffd-983a-e431-83c6-00f5e3249834";
var lookupTable = projectContext.LookupTables.GetById(objectIdOfTheLookupTable);

to get the reference directly, instead of a lookup by name.

In the next example, we delete a custom field by name. Again, we use two batches, one for the lookup of the custom field by name, and a second one for the deletion itself. As in the former example, we can reduce the number of batches to one, if we know the ID of the field, and get it either by the GetByGuid or by the GetById method,

  1. using (var projectContext = new ProjectContext(pwaUrl))
  2. {
  3.     projectContext.Load(projectContext.CustomFields, cfs => cfs.Include(cf => cf.Name));
  4.     projectContext.ExecuteQuery();
  6.     var custField = projectContext.CustomFields.First(cf => cf.Name == "FieldName1");
  8.     custField.DeleteObject();
  9.     projectContext.ExecuteQuery();
  10. }

If you have to delete more fields, you don’t need to send a separate request (or two requests) for each deletion. If you use this last example, you can reduce the number of requests for the field removal:

  1. var fieldNames = new List<string> { "FieldName1", "FieldName2", "FieldName3" };
  3. using (var projectContext = new ProjectContext(pwaUrl))
  4. {
  5.     projectContext.Load(projectContext.CustomFields, cfs => cfs.Include(cf => cf.Name));
  6.     projectContext.ExecuteQuery();
  8.     // query made case insensitive
  9.     fieldNames.ConvertAll(fn => projectContext.CustomFields
  10.         .FirstOrDefault(cf => cf.Name.ToLower() == fn.ToLower()))
  11.         .Where(fn => fn != null).ToList()
  12.         .ForEach(fn => fn.DeleteObject());
  14.     projectContext.ExecuteQuery();
  15. }

November 22, 2014

Managing Project Server Lookup Tables via the Managed Client Object Model

Filed under: ALM, Managed Client OM, Project Server — Tags: , , — Peter Holpar @ 08:25

In my former post I described, how we can manage Project Server event handlers via the managed client object model. In the current post I show you how to do that in case of lookup tables.

As I wrote in the previous post, we can deploy Project Server entities declaratively, via SharePoint solution packages (WSP), see that post for the details. An example for lookup tables is included below:

  1. <?xml version="1.0" encoding="utf-8" ?>
  2. <ProjectServerEntities xmlns="">
  3.   <LookupTables>
  4.     <LookupTable Id="E7397277-1AB0-4096-B2DD-57029A055BA4" Name="YourLookupTable">
  5.       <SortOrder>UserDefined</SortOrder>
  6.       <Mask>
  7.         <MaskLength>0</MaskLength>
  8.         <MaskType>CHARACTERS</MaskType>
  9.         <MaskSeparator>.</MaskSeparator>
  10.       </Mask>
  11.       <LookupEntry>
  12.         <Id>8D3CFDD8-566B-4FA4-8C01-E315BCF13E74</Id>
  13.         <ParentId />
  14.         <SortIndex>86</SortIndex>
  15.         <Value>
  16.           <TextValue>Value1</TextValue>
  17.         </Value>
  18.       </LookupEntry>
  19.       <LookupEntry>
  20.         <Id>8A913280-C8D5-4E85-AE73-526EF5ABC686</Id>
  21.         <ParentId />
  22.         <SortIndex>100</SortIndex>
  23.         <Value>
  24.           <TextValue>Value2</TextValue>
  25.         </Value>
  26.       </LookupEntry>
  27.       <LookupEntry>
  28.         <Id>C89A98F0-795A-49EC-9031-BA892CE0D610</Id>
  29.         <ParentId />
  30.         <SortIndex>114</SortIndex>
  31.         <Value>
  32.           <TextValue>Value3</TextValue>
  33.         </Value>
  34.       </LookupEntry>     
  35.     </LookupTable>
  36.   </LookupTables>
  37. </ProjectServerEntities>

Alternatively, we can deploy our entities (including the lookup table) via the managed client object model. I prefer this approach to the declarative option, as I feel more control on the process of what / when deployed / retracted.

First, we can list the lookup tables (including value sets) using the code below:

  1. using (var projectContext = new ProjectContext(pwaUrl))
  2. {
  4.     projectContext.Load(projectContext.LookupTables,
  5.         lts => lts.Include(
  6.                     lt => lt.Name,
  7.                     lt => lt.Id,
  8.                     lt => lt.FieldType,
  9.                     lt => lt.Entries.Include(
  10.                         lte => lte.FullValue,
  11.                         lte => lte.Id)));
  12.     projectContext.ExecuteQuery();
  14.     projectContext.LookupTables.ToList().ForEach(lt =>
  15.         {
  16.             Console.WriteLine("LT: Name [{0}], Id [{1}], FieldType [{2}]", lt.Name, lt.Id, lt.FieldType);
  17.             lt.Entries.ToList().ForEach(lte => Console.WriteLine("LTE: Name [{0}], Id [{1}]", lte.FullValue, lte.Id));
  19.         });
  20. }

The next code creates a new lookup table including three entries:

  1. var lookupTableName = "YourLookupTable";
  2. var values = new List<string> {
  3.                     "Value1",
  4.                     "Value2",
  5.                     "Value3"
  6.                 };
  8. using (var projectContext = new ProjectContext(pwaUrl))
  9. {
  11.     var si = 0;
  12.     var entries = values.ConvertAll(v => new LookupEntryCreationInformation
  13.     {
  14.         Id = Guid.NewGuid(),
  15.         Value = new LookupEntryValue { TextValue = v },
  16.         SortIndex = 100 + (si++) * 10
  17.     });
  19.     LookupTableCreationInformation ltci = new LookupTableCreationInformation
  20.     {
  21.         Id = Guid.NewGuid(),
  22.         Name = lookupTableName,
  23.         SortOrder = LookupTableSortOrder.UserDefined,
  24.         Masks = new List<LookupMask> { new LookupMask { Length = 0, MaskType = LookupTableMaskSequence.CHARACTERS, Separator = "." } },
  25.         Entries = entries
  26.     };
  28.     projectContext.LookupTables.Add(ltci);
  29.     projectContext.LookupTables.Update();
  30.     projectContext.ExecuteQuery();
  31. }

The code below removes an existing entry from a lookup table and inserts a new one into the entries:

  1. using (var projectContext = new ProjectContext(pwaUrl))
  2. {
  4.     projectContext.Load(projectContext.LookupTables, lts => lts.Include(lt => lt.Name, lt => lt.Entries));
  5.     projectContext.ExecuteQuery();
  7.     var lookupTable = projectContext.LookupTables.First(lt => lt.Name == "YourLookupTable");
  9.     // this value should be removed
  10.     lookupTable.Entries.Remove(lookupTable.Entries.First(pe => pe.FullValue == "ValueToRemove"));
  12.     // and a new value has to be created
  13.     lookupTable.Entries.Add(new LookupEntryCreationInformation
  14.     {
  15.         Id = Guid.NewGuid(),
  16.         Value = new LookupEntryValue { TextValue = "ValueToCreate" },
  17.         SortIndex = 165
  18.     });
  20.     projectContext.LookupTables.Update();
  21.     projectContext.ExecuteQuery();
  22. }

The last sample illustrates how we can remove a set of lookup tables based on their name:

  1. var tabledNames = new List<string> { "YourLookupTable", "AnotherLookupTable" };
  3. using (var projectContext = new ProjectContext(pwaUrl))
  4. {
  6.     projectContext.Load(projectContext.LookupTables, lts => lts.Include(lt => lt.Name));
  7.     projectContext.ExecuteQuery();
  9.     // query made case insensitive
  10.     tabledNames.ConvertAll(tn => projectContext.LookupTables
  11.         .FirstOrDefault(lt => lt.Name.ToLower() == tn.ToLower()))
  12.         .Where(lt => lt != null).ToList()
  13.         .ForEach(lt => lt.DeleteObject());
  15.     projectContext.ExecuteQuery();
  16. }

In my next post I describe how to work with enterprise custom fields using the managed client object model.

November 12, 2014

Managing Project Server Event Handlers via the Managed Client Object Model

Project Server events provide a great way to extend the functionality of the product by custom logic. For example, you can write code that will be executed if the web site of the project was created and customize the site with features that can not be integrated into the project site template itself, or respond to the deletion of the project and perform some kind of housecleaning, that is not part of the default deletion, like removing custom created project-specific SharePoint groups.

There is a page in the Central Administration that supports the event handler maintenance (see Central Administration / General Application Settings / PWA Settings (Manage) / Server Side Event Handlers, illustrated below).


There is unfortunately a major Application Lifecycle Management (ALM) related issue with this approach: It is a manual process that cannot be easily automated as part of the solution deployment.

Alternatively, one can deploy event handlers as part of a SharePoint package, as stated in the Project Server entity provisioning schema section of the Project Server programmability article. This method is described and demonstrated in the presentation Developer to developer – learn the Microsoft Project Online and Server extensibility (authors: Bill Avery and Chris Givens), see further details in section “Project Server App Packages” beginning at slide 75 of the presentation.

A sample of the Project Server entity provisioning XML including an event handler registration:

  1. <?xml version="1.0" encoding="utf-8" ?>
  2. <ProjectServerEntities xmlns="">
  3.   <EventHandlers>
  4.     <EventHandler Id="6B92EF26-25CA-4716-9352-67FC2EF57BE3" Name="PWSCreated">
  5.       <EventName>WssInteropWssWorkspaceCreated</EventName>
  6.       <Description>Called when a new PWS site was created</Description>
  7.       <EndpointUrl></EndpointUrl>
  8.       <AssemblyName>$SharePoint.Project.AssemblyFullName$</AssemblyName>
  9.       <ClassName>Customer.EPM.PSEventHandler</ClassName>
  10.       <CancelOnError>false</CancelOnError>
  11.     </EventHandler>
  12.   </EventHandlers>
  13. </ProjectServerEntities>

Note the EndpointUrl element, that contains only a dummy value in this case. In this case we use a local event handler implemented in a custom assembly, however we could use remote event handlers as well. This element is optional by the XSD of the Project Server entity provisioning schema, however, if we did not include this in the XML, we would receive a deployment error on the feature activation:

Value cannot be null. Parameter name: uriString

An empty value causes a similar error in the feature receiver:

Invalid URI: The URI is empty.

If the URL is not well formatted:

Invalid URI: The format of the URI could not be determined.

Formerly I applied this approach to deploy my event handlers, lookup tables and enterprise custom fields, but it turned out quickly, that whenever I deploy a new version of the package, the former entities are retracted and deployed again, resulting in a loss of information, for example, on tasks, projects and resources that already had a re-deployed enterprise custom fields assigned. In a developer environment it was only inconvenient, but in test and production system I found that simply unacceptable.

So I decided to write my own tools to list, register and remove Project Server event handlers using the managed client object model.

First, let’s see how to enumerate the event handlers and dump the most important properties:

  1. using (var projectContext = new ProjectContext(pwaUrl))
  2. {
  3.     projectContext.Load(projectContext.EventHandlers, ehs => ehs.Include(
  4.         eh => eh.Id,
  5.         eh => eh.Event,
  6.         eh => eh.AssemblyName,
  7.         eh => eh.ClassName,
  8.         eh => eh.Order));
  9.     projectContext.ExecuteQuery();
  11.     projectContext.EventHandlers.ToList().ForEach(eh =>
  12.         Console.WriteLine("Event \r\nEvent Handler [Id: {3} AssemblyName: {4} ClassName: {5} Order: {6}]\r\n",
  13.         eh.Event.SourceName, eh.Event.EventName, eh.Event.Id, eh.Id, eh.AssemblyName, eh.ClassName, eh.Order));
  14. }

The output in our case:


Using the code below, we can register our custom event handler. To be able to use the predefined values in the PSEventID enumeration, you should include a reference to the Microsoft.Office.Project.Server.Library.dll assembly.

  1. using (var projectContext = new ProjectContext(pwaUrl))
  2. {
  3.     var ehci = new EventHandlerCreationInformation
  4.     {
  5.         Id = projDeletedEventHandlerId,
  6.         Name = "ProjDeleting",
  7.         AssemblyName = "Customer.EPM, Version=, Culture=neutral, PublicKeyToken=b28db3dcb95229e1",
  8.         ClassName = "Customer.EPM.ProjectEvents",
  9.         Description = "Called when a project is being deleted",
  10.         EventId = (int)PSEventID.ProjectDeleting, // value is 49, handling ProjectEventReceiver.OnDeleting
  11.         Order = 100,
  12.         CancelOnError = false
  13.     };
  15.     projectContext.EventHandlers.Add(ehci);
  16.     projectContext.EventHandlers.Update();
  17.     projectContext.ExecuteQuery();
  18. }

Note: this code only submits the requests for the event handler registration- The registration itself is performed asynchronously in a background process, so there is a delay after running the code until the event handler appears in the list.

Finally, if you don’t need the event handler any more, you can remove it using the following code (assuming you already know the ID of the event handler, for example, from the output of the event handler enumeration example):

  1. var projDeletedEventHandlerId = new Guid("0FB1389D-E620-4062-A99B-9E5716CC958E");
  3. using (var projectContext = new ProjectContext(pwaUrl))
  4. {
  5.     var eh = projectContext.EventHandlers.GetByGuid(projDeletedEventHandlerId);
  6.     projectContext.EventHandlers.Remove(eh);
  7.     projectContext.EventHandlers.Update();
  8.     projectContext.ExecuteQuery();
  9. }

A similar code for unregistering the event can be found here, but the sample above is a bit simpler, using only a single ExecuteQuery call. You might need the double call if you don’t know the ID of the event handler yet. In this case you should look up the event handler by name, or by the type of the event it handles first, and it requires a former ExecuteQuery call to get the complete list of the event handlers. Then in the next step, you can already remove the event handler as illustrated above.

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,


the result looks like this:


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.


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"
  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
  18. $values | % {
  19.     $fields = $_ -split '\t+'
  20.         $level = $fields[0]
  21.         $text = $fields[1]
  22.         $desc = $fields[2]
  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.     }
  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)
  58.     $lastGuid = $guid
  59.     $lastLevel = $level
  60. }
  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.             {
  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.

Create a free website or blog at