Project Server - Create Enterprise Custom Fields and Server Side Event Handlers using PowerShell

Article
12/13/2010

After thinking for some time, I am finally writing my first blog. I am working
in a project where I have to write some scripts to deploy Project Server components
(custom fields and server side events). I came up with the following script.

I am using the Project Web Access (PWA) services to create the custom fields
and the server side events. There are three approaches to use the methods within
the services:

Create any .Net application (console/win forms), add the reference to the web services,
generate the proxy class and compile it to create the assembly. That again can be
referred by PowerShell.
Install the Microsoft SDK on the server where we will be running the scripts. That
will provide us the utilities "wsdl" and "csc" which can be used to generate the
assembly from the web services and load into PowerShell.
We do not have Visual Studio, and we do not want to install Microsoft SDK. Just
.Net framework, and SharePoint with Project Server is available.

I will be writing the scripts for the third approach, as that will be applicable
in the real world scenario specially with the difficult clients.

What I have done is to segregate the scripts and the custom variables into two files.
One PowerShell file which contains script and one Xml file which has all the required
components.

Step 1 - Load Project Server Custom Variable Xml
File, and set the PowerShell Variables

I am using a Xml file to set all the variables, thus any small change in the application
configuration (different from server to server and deployment to deployment) there
is minimal requirement to change the scripts, Here I load one Xml file, read the
contents and set some PowerShell variables which are used later in the scripts.

     #Loading Xml file.

    $projectServerXmlPath = ".\ProjectServerCustomVariables.xml"
    [xml]$projectServerDeploymentXml = Get-Content $projectServerXmlPath
    if ($projectServerDeploymentXml -eq $null) { return }    

    $sharePointManagedAccount = $projectServerDeploymentXml.PwaCustomVariables.ProjectServerParameters.SPManagedAccount
    $pwaWebAppUrl = $projectServerDeploymentXml.PwaCustomVariables.ProjectServerParameters.PwaWebAppUrl + ":" + $projectServerDeploymentXml.PwaCustomVariables.ProjectServerParameters.PwaWebAppUrlPort
    $pwaSiteCollectionUrl = $pwaWebAppUrl + "/" + $projectServerDeploymentXml.PwaCustomVariables.ProjectServerParameters.SiteCollectionName
    $pwaPrimaryDBServer = $projectServerDeploymentXml.PwaCustomVariables.ProjectServerParameters.PrimaryDBServer
    $pwaReportingDBServer = $projectServerDeploymentXml.PwaCustomVariables.ProjectServerParameters.ReportingDbserver
    $siteCollectionAdmin = [Environment]::UserDomainName + "\" + [Environment]::UserName    

    #Database names to be used by the PWA site
    $publishedDbname = "ProjectServer_Published_" + $projectServerDeploymentXml.PwaCustomVariables.ProjectServerParameters.SiteCollectionName + "_" + $projectServerDeploymentXml.PwaCustomVariables.ProjectServerParameters.PwaWebAppUrlPort
    $archiveDbname = "ProjectServer_Archive_" + $projectServerDeploymentXml.PwaCustomVariables.ProjectServerParameters.SiteCollectionName + "_" + $projectServerDeploymentXml.PwaCustomVariables.ProjectServerParameters.PwaWebAppUrlPort
    $draftDbname = "ProjectServer_Draft_" + $projectServerDeploymentXml.PwaCustomVariables.ProjectServerParameters.SiteCollectionName + "_" + $projectServerDeploymentXml.PwaCustomVariables.ProjectServerParameters.PwaWebAppUrlPort
    $reportingDbname = "ProjectServer_Reporting_" + $projectServerDeploymentXml.PwaCustomVariables.ProjectServerParameters.SiteCollectionName + "_" + $projectServerDeploymentXml.PwaCustomVariables.ProjectServerParameters.PwaWebAppUrlPort

Step 2 - Create SharePoint Web Application and Project
Server Web Site (PWA)

We have the variables set. Assuming its a clean deployment, we are deleting existing
Project Server Web Site if it already exists, delete the SharePoint web application
and recreates them. These four steps normally take around 8-10 minutes. The Creation
of the PWA site takes long time, as it provisions 4 databases.

Loading SharePoint PSSnapin

     Write-Host "Loading SharePoint PowerShell Snapin"
    Add-PSSnapin Microsoft.sharepoint.powershell
    #Check if the SharePoint Web application already exists
    $webApp = Get-SPWebApplication $pwaWebAppUrl -ErrorAction SilentlyContinue
    if($webApp -ne $null)
    {
        #Check if PWA site is already created
        $pwaWebApp = Get-SPProjectWebInstance -Url $pwaSiteCollectionUrl -ErrorAction SilentlyContinue
        if($pwaWebApp -ne $null)
        {
            #Delete the existing PWA instance
            Write-Host "Deleting existing PWA site"
            Remove-SPProjectWebInstance -Url $pwaSiteCollectionUrl -KeepSiteCollection:$false -Wait:$true
        }
        #Delete the existing Web Application
        Write-Host "Deleting PWA Web application"
        $webApp | Remove-SPWebApplication -DeleteIISSite:$true -RemoveContentDatabases:$true -Confirm:$false

   }       

   Write-Host "Creating SharePoint Web application"

   New-SPWebApplication -Name "PWA Web App" -ApplicationPool "PwaAppPool" -ApplicationPoolAccount Get-SPManagedAccount $sharePointManagedAccount) -URL $pwaWebAppUrl
   Write-Host "Creating PWA Web Site"

   $pwaWebApp = New-SPProjectWebInstance -Url $pwaSiteCollectionUrl -AdminAccount $siteCollectionAdmin -PrimaryDbserver $pwaPrimaryDBServer -PublishedDbname $publishedDbname -ArchiveDbname $archiveDbname -DraftDbname $draftDbname -ReportingDbserver $pwaReportingDBServer -ReportingDbname $reportingDbname -Wait:$true

Step 3 - Create PowerShell function, creating an
assembly from the WSDL at run time

After finishing step 2, we have the base lined application ready. As I mentioned,
I do not have precompiled assembly for the PWA web services, and neither I have
the Windows SDK installed. Still I will need some steps to extract the classes from
WSDL, create the assembly. The following functions does the same thing. The following
function is referred from here.

 Write-Host "Creating PowerShell Functions"
function GetWebServiceAssembly 
{
 #Parameters being passed: WSDL URL and the Assembly being referred in the WSDL.
 param([string] $webServiceUrl, [string[]] $referenceAssemblies)     

    #Loading Web.Services assembly, which is used to read the WSDL
  [void] [Reflection.Assembly]::LoadWithPartialName("System.Web.Services")
    $wc = new-object System.Net.WebClient  
 $wc.UseDefaultCredentials = $true
   $wsdlStream = $wc.OpenRead($webServiceUrl) 
 $serviceDescription  =  [Web.Services.Description.ServiceDescription]::Read($wsdlStream)
    $wsdlStream.Close()     

    #Following lines load the WSDL, generate cSharp Code, and set the parameters necessary for code compilation
 $serviceNamespace = New-Object System.CodeDom.CodeNamespace  
   $codeCompileUnit = New-Object System.CodeDom.CodeCompileUnit
    $serviceDescriptionImporter =   New-Object Web.Services.Description.ServiceDescriptionImporter
  $serviceDescriptionImporter.AddServiceDescription($serviceDescription, $null, $null)
    [void] $codeCompileUnit.Namespaces.Add($serviceNamespace)
   [void] $serviceDescriptionImporter.Import($serviceNamespace, $codeCompileUnit)
  $generatedCode = New-Object Text.StringBuilder
  $stringWriter = New-Object IO.StringWriter $generatedCode
   $provider = New-Object Microsoft.CSharp.CSharpCodeProvider 
 $provider.GenerateCodeFromCompileUnit($codeCompileUnit, $stringWriter, $null)
   $references = $referenceAssemblies
  $compilerParameters = New-Object System.CodeDom.Compiler.CompilerParameters 
    $compilerParameters.ReferencedAssemblies.AddRange($references)
  $compilerParameters.GenerateInMemory = $true         

   #Compile the code in memory, and create an Assembly (object as it is in memory,and not files being used).
   #In case compilation failes, it will return null object, else return the compiled assembly
  $compilerResults = $provider.CompileAssemblyFromSource($compilerParameters, $generatedCode)
 if($compilerResults.Errors -ne $null)
   {
       #compilation failed - do exception handling
     return $null
    }

   $compiledAssembly = $compilerResults.CompiledAssembly
   return $compiledAssembly
}

Step 4 - Create Enterprise Project Server
Custom Fields

We have the PWA instance, we have the function to read wsdl and return assembly,
and we have the variables ready. The web service which is used to create the Custom
Fields is

 https://<PWA Site Instance URL>/_vti_bin/psi/CustomFields.asmx

Steps followed here are getting the assembly object of above URL using the function
from Step 2, creating service class object, set variables as required and call service
to finish the creation. PWA uses the (own) dataset object to save the Custom Fields,
and same is passed to the service object. So instead of having multiple service
calls for each custom field, we populate the dataset using our Xml and single service
call creates the Custom Fields. The Script is:

 Write-Host "Creating custom fields"
#Service URL for Custom Fields
$custonFieldsServiceUrl = $pwaSiteCollectionUrl + "/_vti_bin/psi/CustomFields.asmx?wsdl"

#Reference assemblies to be used to compile the above WSDL
$pwaReferenceAssemblies = @("System.dll", "System.Web.Services.dll", "System.Xml.dll", "System.Data.dll")
$pwaServiceAssembly = GetWebServiceAssembly $custonFieldsServiceUrl $pwaReferenceAssemblies

#If Assembly oject is null, error is there while compiling the WSDL. 
if($pwaServiceAssembly -ne $null)
{
 #Create Service Object
  $pwaCustomFieldService = $pwaServiceAssembly.CreateInstance("CustomFields", $true)
  #Create Dataset object
  $customFieldDataSet = $pwaServiceAssembly.CreateInstance("CustomFieldDataSet", $true)
   #Set the service to use the logged in user credentials. As the logged in user is set as Administrator for PWA, the service will be called without giving authentication error
   $pwaCustomFieldService.UseDefaultCredentials = $true

    #GUIDs for the different Entities
   $taskEntityUniqueId = [Microsoft.Office.Project.Server.Library.EntityCollection]::Entities.TaskEntity.UniqueId
  $projectEntityUniqueId = [Microsoft.Office.Project.Server.Library.EntityCollection]::Entities.ProjectEntity.UniqueId
    $resourceEntityUniqueId = [Microsoft.Office.Project.Server.Library.EntityCollection]::Entities.ResourceEntity.UniqueId      

    #In case the script deletes and recreates the PWA site, the databases associated with it are not deleted. In that case, if the Custom Fieldsto be created already exists, delete them.
  Write-Host "Delete custom fields if already exists" 
    $customFieldTaskEntityType = $pwaCustomFieldService.ReadCustomFieldsByEntity($taskEntityUniqueId)
   $customFieldResourceEntityType = $pwaCustomFieldService.ReadCustomFieldsByEntity($resourceEntityUniqueId)
   $customFieldProjectEntityType = $pwaCustomFieldService.ReadCustomFieldsByEntity($projectEntityUniqueId) 

    $guidCollection = New-Object System.Collections.ArrayList

   foreach($customField in $projectServerDeploymentXml.PwaCustomVariables.CustomFields.CustomField)
    {
       $customFieldFound = $false
      foreach($customFieldEntity in $customFieldTaskEntityType.CustomFields)
      {
           if($customFieldEntity.MD_PROP_NAME.Equals($customField.Name, [StringComparison]::CurrentCultureIgnoreCase))
         {
               $guidCollection.Add($customFieldEntity.MD_PROP_UID)
             $customFieldFound = $true
               break
           }
       }       

        if($customFieldFound)
       {
           continue
        }       

        foreach($customFieldEntity in $customFieldResourceEntityType.CustomFields)
      {
           if($customFieldEntity.MD_PROP_NAME.Equals($customField.Name, [StringComparison]::CurrentCultureIgnoreCase))
         {
               $guidCollection.Add($customFieldEntity.MD_PROP_UID)
             $customFieldFound = $true
               break
           }
       }       

        if($customFieldFound)
       {
           continue
        }       

        foreach($customFieldEntity in $customFieldProjectEntityType.CustomFields)
       {
           if($customFieldEntity.MD_PROP_NAME.Equals($customField.Name, [StringComparison]::CurrentCultureIgnoreCase))
         {
               $guidCollection.Add($customFieldEntity.MD_PROP_UID)
             $customFieldFound = $true
               break
           }
       }
   }   

    if($guidCollection.Count -ne 0)
 {
       $pwaCustomFieldService.CheckOutCustomFields($guidCollection)
        $pwaCustomFieldService.DeleteCustomFields($guidCollection)
  }   

    #Creating the Custom Fields now
 foreach($customField in $projectServerDeploymentXml.PwaCustomVariables.CustomFields.CustomField)
    {
       $entityGuid = [System.Guid]::Empty
      $lookUpTableGuid = [System.Guid]::Empty     

        $customFieldRow = $customFieldDataSet.CustomFields.NewCustomFieldsRow()
     $customFieldRow.MD_PROP_UID = [Guid]::NewGuid()     

        if($customField.Entity.Equals("Task", [System.StringComparison]::CurrentCultureIgnoreCase))
     {
           $customFieldRow.MD_ENT_TYPE_UID = [Microsoft.Office.Project.Server.Library.EntityCollection]::Entities.TaskEntity.UniqueId
      }
       elseif($customField.Entity.Equals("Resource", [System.StringComparison]::CurrentCultureIgnoreCase))
     {
           $customFieldRow.MD_ENT_TYPE_UID = [Microsoft.Office.Project.Server.Library.EntityCollection]::Entities.ResourceEntity.UniqueId
      }
       else
        {
           $customFieldRow.MD_ENT_TYPE_UID =[Microsoft.Office.Project.Server.Library.EntityCollection]::Entities.ProjectEntity.UniqueId
        }

       $customFieldRow.MD_PROP_NAME = $customField.Name
        $customFieldRow.MD_PROP_IS_REQUIRED = $false
        $customFieldRow.MD_PROP_IS_LEAF_NODE_ONLY = $false

      if($customField.Type.Equals("Date", [System.StringComparison]::CurrentCultureIgnoreCase))
       {
           $customFieldRow.MD_PROP_TYPE_ENUM = [Microsoft.Office.Project.Server.Library.PSDataType]::DATE
      }
       elseif($customField.Type.Equals("Cost", [System.StringComparison]::CurrentCultureIgnoreCase))
       {
           $customFieldRow.MD_PROP_TYPE_ENUM = [Microsoft.Office.Project.Server.Library.PSDataType]::COST
      }
       elseif($customField.Type.Equals("Number", [System.StringComparison]::CurrentCultureIgnoreCase))
     {
           $customFieldRow.MD_PROP_TYPE_ENUM = [Microsoft.Office.Project.Server.Library.PSDataType]::NUMBER
        }
       elseif($customField.Type.Equals("Flag", [System.StringComparison]::CurrentCultureIgnoreCase))
       {
           $customFieldRow.MD_PROP_TYPE_ENUM = [Microsoft.Office.Project.Server.Library.PSDataType]::YESNO
     }
       elseif($customField.Type.Equals("Duration", [System.StringComparison]::CurrentCultureIgnoreCase))
       {
           $customFieldRow.MD_PROP_TYPE_ENUM = [Microsoft.Office.Project.Server.Library.PSDataType]::DURATION
      }
       else    
        {
           $customFieldRow.MD_PROP_TYPE_ENUM = [Microsoft.Office.Project.Server.Library.PSDataType]::STRING
        }           

        $customFieldRow.MD_PROP_DESCRIPTION = $customField.Description
      $customFieldRow.SetMD_LOOKUP_TABLE_UIDNull()
        $customFieldRow.SetMD_PROP_DEFAULT_VALUENull()      

        #Populating Custom Fields DataSet
       $customFieldDataSet.CustomFields.Rows.Add($customFieldRow)
  }

   #Calling the Service to Create Custom Fields in PWA.
    $pwaCustomFieldService.CreateCustomFields($customFieldDataSet, $false, $true);
}

Step 5 - Create Server Side Event Handlers

Last step is to create the Event Handlers. It is same as the Custom Fields, as Service
object and dataset objects are created using the assembly of wsdl, and after populating
the dataset we pass that to the Service object to update PWA. The only difference
is that we do not need to delete the existing event handlers, as it over writes
the existing one.

The Service URL for the Event Handler service is

 https://<PWA Site Instance URL>/_vti_bin/psi/Events.asmx

And the script used is:

 #Service URL for Server Side Event Handlers
Write-Host "Creating server side event handlers"
$pwaEventsUrl = $pwaSiteCollectionUrl + "/_vti_bin/psi/Events.asmx?wsdl"

#Creating assembly reference for above URL
$pwaReferenceAssemblies = @("System.dll", "System.Web.Services.dll", "System.Xml.dll", "System.Data.dll")
$pwaServiceAssembly = GetWebServiceAssembly $pwaEventsUrl $pwaReferenceAssemblies

#If assembly object is null, compilation failed.
if($pwaServiceAssembly -ne $null)
{
  #Creating service object
    $eventService = $pwaServiceAssembly.CreateInstance("Events", $true)
 $eventService.UseDefaultCredentials = $true 
    #Creating Events Dataset object
 $eventHandlerDataSet = $pwaServiceAssembly.CreateInstance("EventHandlersDataSet", $true)    

    foreach($eventHandler in $projectServerDeploymentXml.PwaCustomVariables.CustomEventsHandlers.EventHandler)
  {
       $eventHandlerRow = $eventHandlerDataSet.EventHandlers.NewEventHandlersRow()     

        $eventHandlerRow.AssemblyName = $eventHandler.AssemblyName
      $eventHandlerRow.ClassName = $eventHandler.ClassName
        $eventHandlerRow.Description = $eventHandler.Description        

        $eventId = $eventHandler.EventSource + $eventHandler.EventName  
        $eventIdType = [Microsoft.Office.Project.Server.Library.PSEventID]::$eventId.GetType()
      $eventEnum = [Enum]::Parse($eventIdType, $eventId, $true)   

        $eventHandlerRow.EventId = $eventEnum
       $eventHandlerRow.EventHandlerUid = [Guid]::NewGuid()
        $eventHandlerRow.Name = $eventHandler.Name
      $eventHandlerRow.Order = $eventHandler.Order    

        #Populating the Dataset
     $eventHandlerDataSet.EventHandlers.Rows.Add($eventHandlerRow)
   }
   #Calling service to create event handlers
   $eventService.CreateEventHandlerAssociations($eventHandlerDataSet)
}

The above steps will do the required. To finalize, remove the SharePoint PSSnapin
added, and restarting the Project Server services.

 Write-Host "Unloading SharePoint PowerShell Snapin"
Remove-PSSnapin Microsoft.sharepoint.powershell

Write-Host "Restarting Microsoft Project Server services"
Restart-Service -Name ProjectEventService14
Restart-Service -Name ProjectQueueService14

That's all for now. Creating Project Server users using the PowerShell scripts will
be some other time. The sample xml file and the script file are attached for the reference.

ProjectServerDeployment.zip

Comments

Anonymous
November 21, 2011
Thanks Manish, Just what I was looking for. Thanks for sharing!

Share via

Project Server - Create Enterprise Custom Fields and Server Side Event Handlers using PowerShell

Comments

Additional resources