Microsoft Project Server 2013: how to programmatically change resource assignment for specific time period

Introduction

Let's say we have a project in MS Project Server 2013 which uses some material resources. For example, it can be a detailed construction plan for some building, something like this:

Now let's say we want to change Bricks quantity for Tuesday from 500 to 550 programmatically. Why? This may happen, for example, if initial builder report we used was inaccurate, and the builder just uploaded corrected report as Excel document or plain text. We don't want to edit Project file manually each time when it happens.

So, we will have to use some API to manipulate data on Project Server.

MS Project Server API overview

There are several API available to us. Each of them has its own advantages and disadvantages.

PSI (or Project Server Interface) is mighty tool, capable of almost everything on Project Server: create project, delete project, check in, check out, publish, add or remove task or resource, and many other different things.

Simple example of using PSI:

SvcProject.ProjectDataSet projectDs = projectClient.ReadProjectList();
foreach (SvcProject.ProjectDataSet.ProjectRow projectRow in projectDs.Project)
{
    if (projectRow.PROJ_NAME == projectName)
        projectId = projectRow.PROJ_UID;
}

Speaking of our task, PSI does have a method to edit resource assignments (it is called Statusing.UpdateStatus). But unfortunately it has some limitations: it can only change assignment to whole task at once. In our example, it would mean that Bricks quantity would change to 450 for every day of the task, not just for Tuesday. Old values for Monday and Wednesday would be lost, which is not acceptable.

CSOM (or Client-side object model) is the recommended API for Project Server 2013. It includes 5 different APIs for different purposes: a Microsoft .NET CSOM, a Microsoft Silverlight CSOM, a Windows Phone 8 CSOM, a JavaScript object model (JSOM), and an OData service that enables a REST interface.

I didn't go deep into  Silverlight CSOM, Windows Phone CSOM and JSOM, because my goal was to create a .Net console application, so these 3 interfaces will not be described in this article.

Microsoft .NET CSOM has nearly the same possibilities as PSI in managing Project Server. Simple example:

var eptList = projContext.LoadQuery(
    projContext.EnterpriseProjectTypes.Where(
        ept => ept.Name == eptName));
projContext.ExecuteQuery();
projectId = eptList.First().Id;

.Net CSOM has methods to edit resource assignments (they are included into class StatusAssignment), but they have just the same limitations as PSI method described earlier: they can only change assignment to whole task, not to specific time period.

OData (or Open Data Protocol) uses REST (HTTP-based) API for reporting purposes. Data provided with this this API is read-only, so it's impossible to change anything on Project Server using OData at all.

Simple example of using OData:

http://ServerName/ProjectServerName/_api/ProjectData/Projects

Microsoft.Office.Interop.MSProject.dll is a part of an Office family interop assemblies, and it provides API for MS Project client application programmability. Interop assembly uses MS Project process (so, MS project must be running in background), and it is not really strong in MS Project Server management. But it can do anything user can do with MS Project, including editing resource assignment for specific date. So, we will have no choice but to use it for our task.

Simple example of using Project interop assembly:

ApplicationClass app = new ApplicationClass();
app.FileOpen(currentProject, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
    Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
    PjPoolOpen.pjDoNotOpenPool, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
foreach (Task task in wp.app.ActiveProject.Tasks)
    Console.WriteLine(task.Name);
app.FileCloseEx(PjSaveType.pjDoNotSave, false, !app.ActiveProject.ReadOnly);

Application structure

As I mentioned previously, Interop assembly cannot perform server-specific tasks. So different parts of the application will pursue different goals and use different APIs.

1. Force check-in the project we are going to work with. This step is necessary for the application to be able to edit the project. This part will use PSI API

2. Start MS Project client application and connect it to Project Server. Connection will be established using MS Project command line parameters; MS Project will be started using System.Diagnistics.Process assembly

3. Open necessary project on server and make required changes. This step will use Microsoft.Office.Interop.MSProject.dll assembly

4. Save, publish and close the project, then close MS Project client application. This step will use Microsoft.Office.Interop.MSProject.dll assembly, too

Step 1: force check-in

For PSI code to work, we will need ProjectServerServices.dll assembly. MS Project SDK includes source code, .cmd files and detailed manual to compile this assembly.

SDK also includes several examples, which explain how to configure Project Server endpoint. We will need 2 endpoints: Project and QueueSystem:

private const string ENDPOINT_PROJECT = "basicHttp_Project";
private const string ENDPOINT_QUEUESYSTEM = "basicHttp_QueueSystem";
private static SvcProject.ProjectClient projectClient;
private static SvcQueueSystem.QueueSystemClient queueSystemClient;

public bool ForseCheckInProject(string projectName)
{
ConfigClientEndpoints(ENDPOINT_PROJECT);
ConfigClientEndpoints(ENDPOINT_QUEUESYSTEM);
...
}

// Use the endpoints defined in app.config to configure the client.
public static void ConfigClientEndpoints(string endpt)
{
    if (endpt == ENDPOINT_PROJECT)
        projectClient = new SvcProject.ProjectClient(endpt);
    else if (endpt == ENDPOINT_QUEUESYSTEM)
        queueSystemClient = new SvcQueueSystem.QueueSystemClient(endpt);
}    

Project endpoint will be used to force check-in the project:

SvcProject.ProjectDataSet projectDs = projectClient.ReadProjectList();
foreach (SvcProject.ProjectDataSet.ProjectRow projectRow in projectDs.Project)
{
    if (projectRow.PROJ_NAME == projectName)
        projectId = projectRow.PROJ_UID;
}
if (projectId != Guid.Empty)
{
    projectClient.QueueCheckInProject(jobId, projectId, true, sessionId, SESSION_DESC);
    WaitForQueue(queueSystemClient, jobId);
    return true;
}    

QueueSystem will be used to wait for the check-in operation to complete:

static private void WaitForQueue(SvcQueueSystem.QueueSystemClient q, Guid jobId)
{
    SvcQueueSystem.JobState jobState;
    const int QUEUE_WAIT_TIME = 2; // two seconds
    bool jobDone = false;
    string xmlError = string.Empty;
    int wait = 0;

    // Wait for the project to get through the queue.
    // Get the estimated wait time in seconds.
    wait = q.GetJobWaitTime(jobId);

    // Wait for it.
    Thread.Sleep(wait * 1000);
    // Wait until it is finished.

    do
    {
        // Get the job state.
        jobState = q.GetJobCompletionState(out xmlError, jobId);

        if (jobState == SvcQueueSystem.JobState.Success)
        {
            jobDone = true;
        }
        else
        {
            if (jobState == SvcQueueSystem.JobState.Unknown)
            {
                jobDone = true;
                Console.WriteLine("Project was already checked in, operation aborted");
            }
            else if (jobState == SvcQueueSystem.JobState.Failed
                     || jobState == SvcQueueSystem.JobState.FailedNotBlocking
                     || jobState == SvcQueueSystem.JobState.CorrelationBlocked
                     || jobState == SvcQueueSystem.JobState.Canceled)
            {
                // If the job failed, error out.
                throw (new ApplicationException("Queue request failed \"" + jobState + "\" Job ID: " + jobId +
                                                ".\r\n" + xmlError));
            }
            else
            {
                Console.WriteLine("Job State: " + jobState + " Job ID: " + jobId);
                Thread.Sleep(QUEUE_WAIT_TIME*1000);
            }
        }
    } while (!jobDone);
}

Step 2: Start MS Project client application and connect it to Project Server

Before you proceed, make sure that MS Project Server doesn't ask for login and password when you connect to it with MS Project manually. This can be done using Project Server security settings which are out of scope of this article.

string paramList = " /s " + serverURL;
var startInfo = new ProcessStartInfo("C:\\Program Files\\Microsoft Office\\Office15\\WINPROJ.EXE", paramList);
startInfo.WindowStyle = ProcessWindowStyle.Hidden;
startInfo.UseShellExecute = false;
Process.Start(startInfo);

FindWindow and FindWindowEx methods from User32.dll are used by the application to check if MS Project application is fully loaded or not.

WINPROJ.EXE location is extracted from Windows registry.

Step 3: Open project and make required changes

During the debug, I often encountered an error "Application is busy" on this step. So, every small part of the step was surrounded with try/catch block, and the block was put into While cycle with small timeout after each try. If MS Project is busy, we shell wait as long as necessary.

Open project:

ApplicationClass app = new ApplicationClass();
string currentProject = "<>\\" + projName;
app.FileOpen(currentProject, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
    Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
    PjPoolOpen.pjDoNotOpenPool, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

Edit project/resource assignment. This example uses 1 day time period; it can be changed to any other period length

//Loop through all the tasks and resources to match what we are looking for
foreach (Task task in wp.app.ActiveProject.Tasks)
{
    if (task.Name == taskName)
        foreach (Assignment asgt in task.Assignments)
        {
            if (asgt.ResourceName == resourceName)
            {
                TimeScaleValues TSV = asgt.TimeScaleData(updateDate, updateDate.AddDays(1),
                    PjAssignmentTimescaledData.pjAssignmentTimescaledWork,
                    PjTimescaleUnit.pjTimescaleDays, 1);
                TSV[1].Value = newValue;
            }
        }
}

Step 4: Save and close everything

Every command in this step is included into its own try/catch block in the real code:

app.FileSave();
app.PublishAllInformation();
app.FileCloseEx(PjSaveType.pjDoNotSave, false, !app.ActiveProject.ReadOnly);
app.Quit(PjSaveType.pjDoNotSave);
app = null;

Conclusion

This application is a raw prototype, it has many potential problems with performance and security. But I believe it shows how to achieve some complicated goals on MS Project Server by combining several APIs together.

Credits

I used Project Server 2007 Test Data Population Tool source code as a basis, and this MSDN forum thread provided some valuable insights. I am very grateful to authors for their great work.