Share via

Project Server: Refresh calculated fields for all projects via PowerShell PSI calls

  • Article

https://msdnshared.blob.core.windows.net/media/2016/05/0640_NinjaAwardTinyGold.pngGold Award Winner

 

Introduction and problem 

Microsoft Project Portfolio Management (PPM) platform - Project Server is pretty well known for its confusing behaviour both for developers and IT specialists (worth to mention even some official documents: Product documentation "Developers working with the Project Server Interface (PSI) can be confused by this behaviour", The Bat phone: white paper).  

One of the numerous difficulties is with calculated fields refresh. While PWA (Project Web Access) calculated fields and indicators are rather convenient for end-user as a self-service BI (Business Intelligence) elements implementation it needs special efforts in some cases in order to get the values up to date. 

PowerShell solution reduces the number of assets to be managed if compared to .NET assembly implementation but requires translation C#/VB PSI (Project Server Interface) documentation calls into corresponding PowerShell code.  
 
Below is an approach considered which updates, saves and publishes all the projects in order to force a Project Server custom fields recalculation via PowerShell. 

Solution 

Solution scheme is pretty straightforward but contains some Project Server-specific elements that can be depicted as follows: 

Code 

 

#t-sql launch

#xp_cmdshell 'powershell.exe -ExecutionPolicy bypass -Command "& {& ''\T005\Exchange\publishprojects.ps1''}"'

 

function AddDeletePublish($descr) 

{ 

$ProjDataSet.Clear();

$projectTasks = $ProjDataSet.Task;

 

#add task

       write-host -ForegroundColor green "$((New-TimeSpan $t $(Get-Date)).TotalSeconds) sec";$t = get-date;

   Write-Host -ForegroundColor green ("QueueAddToProject $descr ...");

      $NewTaskGuid = [System.Guid]::NewGuid()

      $NewTaskRow1 = $projectTasks.NewTaskRow();

         $NewTaskRow1.PROJ_UID = $projectUid;

         $NewTaskRow1.TASK_UID = $NewTaskGuid

         $NewTaskRow1.TASK_DUR_FMT = 53;

         $NewTaskRow1.TASK_DUR = 4800;  

         $NewTaskRow1.TASK_NAME = "temp task";

         $NewTaskRow1.TASK_START_DATE = [datetime]::Now;

      $projectTasks.AddTaskRow($NewTaskRow1);

 

 $NewJobGuid = [system.guid]::NewGuid()

# Add the above dataset to project and publish and check in

$svcPSProxy.QueueAddToProject($NewJobGuid, $SessionGuid, $ProjDataSet, $false);

        

       sleep 1

       WaitForJob($NewJobGuid);

 

    # publish project

       write-host -ForegroundColor green "$((New-TimeSpan $t $(Get-Date)).TotalSeconds) sec";$t = get-date;

    Write-Host -ForegroundColor green ("QueuePublish $descr ...");

    $NewJobGuid = [system.guid]::NewGuid()

    $temp = $svcPSProxy.QueuePublish($NewJobGuid, $projectUid, $true,"");

       WaitForJob($NewJobGuid);

};

 

function PublishWithCFupdates($pwaUrl,[System.Guid]$puid) 

{

#http://social.msdn.microsoft.com/Forums/en-US/5c9dc24a-62d3-4ce4-a575-4abf918da610/how-to-create-task-in-pwa-with-powershell?forum=project2010custprog

cls

$t = get-date

write-host -ForegroundColor  green "$((New-TimeSpan $t $(Get-Date)).TotalSeconds) sec";$t = get-date;

write-host -ForegroundColor  green "connecting to services.."

$PSvcUrl = $pwaUrl + "/_vti_bin/PSI/Project.asmx?wsdl" 

$svcPSProxy = New-WebServiceProxy -uri $PSvcUrl -useDefaultCredential;

 

#Queue web service 

$QSvcUrl = $pwaUrl + "/_vti_bin/PSI/QueueSystem.asmx?wsdl" 

$QSvcProxy = New-WebServiceProxy -uri $QSvcUrl -useDefaultCredential; #-credential $Credential

 

write-host -ForegroundColor  green "$((New-TimeSpan $t $(Get-Date)).TotalSeconds) sec";$t = get-date;

Write-Host -ForegroundColor green ("read projects");

 

$EPMTYGUID = [system.guid]::empty

$ProjectList = $svcPSProxy.ReadProjectStatus("$EPMTYGUID","WorkingStore","", "0").Project | format-table proj_uid -hidetableheaders | out-string -stream

if($puid){$projectList = @($puid)}

 

$projects_left_to_process =  $ProjectList.Count;

 

foreach ($projectUid in $ProjectList)

{

      

       Write-Host -ForegroundColor green ("projects to process left $projects_left_to_process");

 

       $projects_left_to_process = $projects_left_to_process - 1;

    if ($projectUid -eq ""){continue;}

       Write-Host -ForegroundColor green ("processing project $projectUid");

 

       $projectUid=[System.Guid]$projectUid;

       $SessionGuid = [System.Guid]::NewGuid() ;

    $SessionDescr = "refresh calculations" ;

   

       write-host -ForegroundColor green "$((New-TimeSpan $t $(Get-Date)).TotalSeconds) sec";$t = get-date;

       Write-Host -ForegroundColor green ("force checkin project...");

       $NewJobGuid = [system.guid]::NewGuid();

    $temp = $svcPSProxy.QueueCheckInProject($NewJobGuid, $projectUid, $true, $SessionGuid, $SessionDescr);

       sleep 1

       WaitForJob($NewJobGuid); 

      

 

       $G = [System.Guid]::NewGuid() 

 

    $temp = $svcPSProxy.CheckOutProject($projectUid, $SessionGuid, $SessionDescr);

       sleep 1

      

       write-host -ForegroundColor green "$((New-TimeSpan $t $(Get-Date)).TotalSeconds) sec";$t = get-date;

       Write-Host -ForegroundColor green ("ReadProject...");

 

       $ProjDataSet = $svcPSProxy.ReadProjectEntities($projectUid,2,0);

      

       AddDeletePublish("1");

       AddDeletePublish("2");

       write-host -ForegroundColor green "$((New-TimeSpan $t $(Get-Date)).TotalSeconds) sec";$t = get-date;

       Write-Host -ForegroundColor green ("ReadProject...");

 

       $ProjDataSet = $svcPSProxy.ReadProjectEntities($projectUid,2,0);

       $tr = @();

       foreach($task in $ProjDataSet.Task)

       {

         if($task.TASK_NAME -eq "temp task")

              {

                    $tr+=$task.TASK_UID;

             };

       };

      

       write-host -ForegroundColor green "$((New-TimeSpan $t $(Get-Date)).TotalSeconds) sec";$t = get-date;

       Write-Host -ForegroundColor green ("QueueDeleteFromProject $descr ...");

 

       $NewJobGuid = [system.guid]::NewGuid()

       $svcPSProxy.QueueDeleteFromProject($NewJobGuid, $SessionGuid, $projectUid,$tr);

       sleep 1

       WaitForJob($NewJobGuid);

 

       # publish project

       write-host -ForegroundColor green "$((New-TimeSpan $t $(Get-Date)).TotalSeconds) sec";$t = get-date;

    Write-Host -ForegroundColor green ("QueuePublish after del ...");

    $NewJobGuid = [system.guid]::NewGuid()

    $temp = $svcPSProxy.QueuePublish($NewJobGuid, $projectUid, $true,"");

       WaitForJob($NewJobGuid);

 

 

    # check in project

    sleep 1

       write-host -ForegroundColor green "$((New-TimeSpan $t $(Get-Date)).TotalSeconds) sec";$t = get-date;

       Write-Host -ForegroundColor green ("QueueCheckInProject ...");

 

    $NewJobGuid = [system.guid]::NewGuid()

    $temp = $svcPSProxy.QueueCheckInProject($NewJobGuid, $projectUid, $false, $SessionGuid, $SessionDescr);

       WaitForJob($NewJobGuid);

}

write-host -ForegroundColor green "$((New-TimeSpan $t $(Get-Date)).TotalSeconds) sec";$t = get-date;

Write-Host -ForegroundColor green ("done. thank you." );

}

  

function WaitForJob([System.Guid]$jobId)

{

    $QUEUE_WAIT_TIME = 2; # two seconds

    $jobDone = $false;

    $xmlError = [string]::Empty;

    $wait = 0;

 

    # Wait for the project to get through the queue.

    # Get the estimated wait time in seconds.

    # $wait = $QSvcProxy.GetJobWaitTime($jobId);

 

    # Wait for it.

    sleep $wait;

    # Wait until it is finished. 

    do

    {

        # Get the job state.

        $jobState = $QSvcProxy.GetJobCompletionState($jobId,[ref]$xmlError);

             Write-Host -ForegroundColor green ("job state: $jobState");

        if ($jobState -eq "Success")

        {

            $jobDone = $true;

        }

        else

        {

            if (($jobState -eq "Unknown") -or ($jobState -eq "Failed")-or ($jobState -eq "FailedNotBlocking")-or ($jobState -eq "CorrelationBlocked")-or ($jobState -eq "Canceled"))

            {

                # If the job failed, error out.

                Write-Host -ForegroundColor red ("failed");

                           break;

                           # Console.WriteLine("Queue request failed \" + jobState + "\ Job ID: " + jobId + ".");

                # throw new Exception("Queue request failed \" + jobState + "\ Job ID: " + jobId + ".");

            }

            else

            {

                #Console.WriteLine("Job State: " + jobState + " Job ID: " + jobId);

                           $cvt = $QUEUE_WAIT_TIME;

                           Write-Host -ForegroundColor green ("waiting $cvt ..." );

                sleep $cvt ;

            }

        }

    }

    while ($jobDone -eq $false);  

}

 

PublishWithCFupdates "http://project.contoso.com/pwa" $args[0]

#PublishWithCFupdates("http://project.contoso.com/pwa")

Restrictions and Usage 

  • The script needs to be tested carefully in pre-production or at least development environment in order to reveal system peculiarities under long-continued load. 

  • Solution was checked and deployed on Project Server 2010, for Project Server 2013 the scheme seems to be the same. 

  • Solutions can be modified for parallel projects processing with corresponding increased load and additional careful testing. 

  • Number of Add-Publish cycles can be increased/reduced in order to comply to corresponding specific custom fields calculation structures. 

Resources 

Product documentation 

Application Lifecycle Management in SharePoint 2010 

What's new for IT pros in Project Server 2013 

Task Start and Finish Dates 
 

Community 
 
How to create task in PWA with Powershell 

See Also 

SSAS, SSIS: Extending Project Server OLAP Cubes via push-mode processing