Importing SharePoint List Data into Project Server 2007 Custom Fields
Article
Summary: Learn how to use the programmability features of Microsoft Office Project Server 2007 and Windows SharePoint Services 3.0 to import SharePoint list data into an enterprise custom field. (40 printed pages)
The book addresses both Project Server 2007 and Microsoft Project Server 2010. This article is adapted from a chapter in that book.
Overview of Project Workspaces and Project Server Programmability Features
In addition to its project management, resource planning, and reporting features, Microsoft Office Project Server 2007 encourages collaboration through a feature named Project Workspaces. This feature provides a central repository where project teams can share project artifacts and other information.
Project Server 2007 automatically collects data from three specialized lists in a project workspace: risks, issues, and deliverables. Users may access this data by using the Project Server data analysis and Reporting database features.
In this article, a hypothetical real-world scenario is used to demonstrate how the programmability features of Project Server 2007 and Windows SharePoint Services 3.0 enable you to customize and extend these products to fulfill the unique business needs of your customers.
You are a senior developer in your organization's Microsoft Enterprise Project Management (EPM) practice. This practice designs, deploys, and customizes Project Server 2007 and related projects for both internal and external customers.
You are currently working on a Project Server 2007 deployment engagement for one of your company's largest clients, Contoso. Moments ago, you received an e-mail message from your team's technical lead informing you of a new "must-have" requirement from the customer.
Your team's project manager has promised Contoso's project sponsor a project custom field named Issue Health, which will contain a red, yellow, or green indicator based on the count of active issues in that project's workspace. The project manager mistakenly assumed that delivering the indicator field was simply a matter of creating a custom field by using a formula based on the Active Issues field.
Although you can easily display the issue count in the Project Center, you cannot reference it in a custom field formula to calculate a key performance indicator (KPI). As the team's senior developer, you must design a solution to fulfill this new requirement.
Design Decisions
According to the customer's requirements, you may not deliver any client-side solutions or any solution that involves manual population of the Issue Health field by Contoso's project managers.
Interacting with the Windows SharePoint Services 3.0 Web services and the Project Server Interface (PSI) by using Microsoft SQL Server Integration Services (SSIS) is possible, but you want to keep the solution simple by limiting the number of systems involved.
Direct exchange of database data between two systems by using SSIS is possible, but Microsoft specifically does not recommend interacting directly with the Windows SharePoint Services 3.0 and Project Server 2007 databases other than the Reporting database (RDB).
Understanding the Eventing Architecture
The Project Server 2007 eventing architecture offers two primary types of events: PSI-based events and RDB-based events. The server raises PSI-based events for many, but not all, PSI methods, and raises RDB-based events during the creation, modification, and deletion of data within the RDB.
The eventing architecture further subdivides PSI-based events into two types: pre-events and post-events. The server raises pre-events immediately before saving data to the database, and raises post-events immediately after saving data to the database.
Note
All RDB-based events are post-events.
You may not cancel either category of post-event, but you may abort a database save operation by cancelling a pre-event. The eventing architecture also automatically cancels a pre-event if an event handler throws an unhandled exception.
For more information about the Project Server 2007 eventing architecture, see Project Server Events.
Selecting a Server-Side Event for Customization
One of your most important tasks when designing a custom event handler is the selection of an event. Although the rich eventing architecture of the PSI may seem overwhelming at first, remember that events correspond to specific operations or events within the platform. In some cases, you may need to isolate a particular operation or event to narrow down your choices. For example, if your requirement is to simply make changes to a project's custom fields when a user saves it to the server, you could potentially choose any one of the following events:
OnSaved
OnUpdating
OnUpdated
OnPublishing
OnPublished
OnCheckIn
To reduce how many choices you have, you should clarify your requirements to align with a particular operation. For example, if your customer is willing to update a project's custom fields only on a publish operation, you have reduced your choices to either the OnPublishing event or the OnPublished event. As a general rule, you should use a pre-event only in situations where you may need to cancel a write operation to a database.
In some cases, you may discover that no available event fits your needs. For example, updating a project's custom fields before the server commits a user's changes to the Draft database is not possible, because there is no OnSaving event. In this situation, you need to either adjust your requirements or design an alternative solution.
Remember that if you change to a different event in the middle of your development cycle, you may need to refactor significant portions of your code depending on the differences between each event's EventArgs parameter.
ActiveIssuesHealth High-Level Design
Contoso's requirements do not dictate that you should prevent a project from being published if the count of active issues cannot be obtained, only that this information should be updated each time the project is published. Therefore, the ActiveIssuesHealth event handler will use the OnPublished event.
As shown in Figure 1, the event handler executes these high-level tasks:
Retrieve the project workspace URL from the PSI.
Retrieve and count the active issues within the project workspace.
Update and publish the project by using the PSI.
Developing the ActiveIssuesHealth Event Handler
With the design finalized, you can start developing the custom event handler. You use the following procedures:
Create the event handler project and set references.
Override the event's base method.
Develop an exception expander for PSI SoapExceptions.
Develop a class to store Project Web Access (PWA) site information.
Develop a class to store workspace information.
Retrieve the project's workspace URL and the issues list URL.
Store and retrieve the event handler's settings.
Retrieve the count of active issues.
Retrieve the GUID of the target custom field.
Create an update payload.
Create a ProjectDerived class for impersonation.
Retrieve the current checkout's SessionUID.
Update and publish the project.
Create an installer to register with Project Server.
Create the Stsadm extension project and set references.
Develop the SetSspName Stsadm extension.
Develop the SetActiveStatusValue Stsadm extension.
Develop the SetTargetCustomField Stsadm extension.
Create the VSeWSS project and set references.
Construct the solution package (.wsp file) for deployment to Windows SharePoint Services 3.0 or SharePoint Server.
Note
This solution requires the Microsoft Office Project Server Events Service identity to have administrator access to Project Server. You should also add this account to the Shared Services Provider (SSP) of Project Server as a process account.
To begin, create the event handler and set references.
Procedure 1. Create the event handler project and set references
Open Visual Studio 2008.
Create a Microsoft .NET Framework 3.5 Class Library project named ActiveIssuesHealth.
Edit the properties for the ActiveIssuesHealth project, enable assembly signing, and create a keyfile.
In Solution Explorer, rename Class1.cs to ActiveIssuesHealthHandler.cs.
In the Add Reference dialog box, add references to the Microsoft.Office.Project.Server.Events.Recievers.dll assembly and the Microsoft.Office.Project.Server.Library.dll assembly. Following is the default location:
%ProgramFiles%\Microsoft Office Servers\12.0\Bin\
Note
If you are developing on a 64-bit server, you should manually browse to drive\Program Files instead of using the %ProgramFiles% environment variable. Because Visual Studio 2008 is a 32-bit application running under the WOW64 x86 emulator, %ProgramFiles% resolves to drive\Program Files (x86) in a 64-bit environment.
Add a reference to Microsoft.SharePoint.dll. Following is the default file location:
%ProgramFiles%\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI\
In the Add Reference dialog box, add a reference to System.Web.Services.dll, listed under the .NET tab.
Add the following lines to the global references section of ActiveIssuesHealthHandler.cs.
In the Add Web Reference dialog box, add a Web reference to the WssInterop PSI Web service named WssInteropSvc. Following is the default Web service location:
The Microsoft.Office.Project.Server.Library namespace contains enumerations and classes that are used by the PSI and event base methods. The Microsoft.Office.Project.Server.Events namespace contains delegates, interfaces, and classes related to events. The Microsoft.SharePoint namespace contains objects that enable you to interact with SharePoint.
When the PSI encounters an error, it returns a SoapException exception instead of the data you requested. The PSI encodes specific error codes and more detailed information about the error within the SoapException object. To extract this data, use the PSClientError class.
Note
In some cases, the PSClientError class does not extract all of the detailed error information from the SoapException object. Additional error information can be found in the SoapException.InnerXML property.
The ExpandPsiException method presented in Procedure 3 always extracts the contents of the exception's InnerXML property to compensate for this limitation in PSClientError. But, this approach may sometimes result in significantly more complex error messages.
For more information, see Resource.CheckOutResources Method (WebSvcResource).
Procedure 3. Create an exception expander for PSI SoapExceptions
In ActiveIssuesHealthHandler.cs, create a method named ExpandPsiException.
Develop the ExpandPsiException method. This method uses the PSClientError class to extract detailed error information from a SoapException exception thrown by the PSI.
Before you can do any work with the PSI, you need to devise some way of obtaining and storing the URL of the Project Server instance that invoked your event handler. Because you use impersonation later in this article, it makes sense to also grab the hostname and protocol.
Although neither of the parameters for the OnPublished method exposes any of this information, the ProjectPostPublishEventArgs class does provide a SiteGuid property. You can use this GUID to create an instance of the SPSite class, which exposes all of this information.
Procedure 4. Develop a class to store PWA site information
In Solution Explorer, add a new class named PwaSiteInfo.cs to the project.
Modify the class declaration of PwaSiteInfo.cs to add an internal access modifier.
Add the following constructor to the class. This constructor uses the SiteGuid from ProjectPostPublishEventArgs to create an SPSite object from which it populates the new instance of PwaSiteInfo with the PWA site's hostname, protocol, and URL.
Many of the methods in this article use classes that implement IDisposable. Although the common language runtime (CLR) contains garbage collection features, Microsoft best practices state that you should not rely upon the garbage collector to release memory allocated to objects that implement IDisposable in a timely manner, if at all. For more information, see Best Practices: Using Disposable Windows SharePoint Services Objects and IDisposable Interface. For more information about the SPSite class, see SPSite Class (Microsoft.SharePoint).
Add a call to the PwaSiteInfo constructor in the OnPublished method's try block.
Now you need some way to store the workspace name and the Issues list name. Although you could easily use one of the various dictionary collections, there is a chance that in the future you may need to store additional data pertaining to a project workspace and its contents. You may even need to implement one or more methods to process this information in some way. Using a class to store this information is the better solution.
Procedure 5. Develop a class to store workspace information
In Solution Explorer, add a new class named ProjectWssInfo.cs to the project.
Modify the class declaration of ProjectWssInfo.cs to add an internal access modifier.
Next, develop a method that retrieves URLs for the project's workspace and Issues list. This information is easily retrievable from the WssInterop PSI Web service by using the ReadWssInfo method. Because you are interested only in the names of the workspace and the workspace's Issues list, you need to change the URLs returned by ReadWssInfo into the desired format.
Procedure 6. Retrieve the project's workspace and Issues list URLs
In ActiveIssuesHealthHandler.cs, create a method named GetPwsInfo.
Develop the GetPwsInfo method by using the WssInterop PSI Web service. This method retrieves the workspace and Issues list URLs by calling ReadWssData for the project GUID that raised the OnPublished event. These URLs are then stripped down to the workspace and Issues list names and returned in the ProjectWssInfo class that you created earlier.
Although many developers feel that the easiest way to store settings is through some sort of external configuration file, I prefer to use the SharePoint SPPropertyBag class. The PropertyBag is a hashtable that SharePoint stores in the Configuration database as a persisted object. This approach eliminates the burden of synchronizing configuration files across servers in a farm. You store three settings:
ActiveStatusValue, which indicates the status value considered "active."
SspName, which you use when updating the project via impersonation.
TargetCustomField, which indicates the project number custom field you update with the active issues count.
Procedure 7. Store and retrieve the event handler's settings
In Solution Explorer, add a new class named HandlerSettings.cs to the project.
Add the following lines to the global references section of the class.
Add three automatic properties to the class: one to store the value that qualifies an issue as active, one to store the SSP name, and another to store the name of the custom field the handler should populate with the count of active issues.
Develop the GetHandlerSettings method. This method retrieves multiple settings by using the GetKeyValue method and bundles them into a HandlerSettings object for return.
Develop the SetHandlerSettings method. This method instantiates the necessary SharePoint classes and encapsulates the SetKeyValue method that you just created.
Next, you must retrieve a count of active issues from the project's workspace. First, load the project workspace by using the SPWeb class. Then, construct a CAML query by using the SPQuery class to filter out any issues with a status other than the designated active status. After constructing the CAML query, pass it to the SPList.GetItems method and return the number of results.
Procedure 8. Retrieve the count of active issues
In ActiveIssuesHealthHandler.cs, create a method named GetActiveIssuesHealth.
Develop the GetActiveIssuesHealth method. This method creates an SPWeb by using the workspace name retrieved earlier, builds an SPQuery object to retrieve only active issues, and returns a count.
Instead of requiring users to designate a target custom field by using its unique identifier, the HandlerSettings class that you developed earlier enables them to use its much friendlier name string. However, the PSI datasets relate custom fields with entities such as projects and resources only by using their unique identifier. This means that you must develop a method that translates a custom field's name into its unique identifier.
Fortunately, the ReadCustomFields method in the CustomFields Web service accepts a filter parameter that limits the amount of data it returns. You can use this functionality to return data for only custom fields with the desired name.
Procedure 9. Retrieve the Guid of the target custom field
In ActiveIssuesHealthHandler.cs, create a method named GetCustomFieldGuid.
Develop the GetCustomFieldGuid method by using the CustomFields PSI Web service. This method uses a filter parameter to find a custom field by name and return its GUID.
Develop the CreateUpdate method by using the Project PSI Web service. This method retrieves the current custom field data for the project, verifies whether the custom field is already defined, and either adds the custom field to the project with the retrieved active issue count or updates the existing custom field with the new active issue count. The dataset containing the modifications is returned by the method.
You must always verify that a custom field is not already defined on a project before you add a new custom field row to the project's ProjectCustomFields table.
Before you can update and publish the project, you need to determine the session identifier of the user's current checkout. You use this identifier when updating the project.
Procedure 12. Retrieve the current checkout's session_uid
In ActiveIssuesHealthHandler.cs, create a method named GetSessionUid.
Develop the GetSessionUid method by using the Project PSI Web service. This method retrieves the current project's information and extracts the session identifier.
Develop the UpdateProject method. If the project was not checked out when Project Server invoked the event handler, you must check it out by using the CheckOutProject method before you can update it. Then, transmit the update for enqueueing by using impersonation and the QueueUpdateProject method. After the update payload is transmitted, republish the project by using the QueuePublish method. Finally, if the project was not checked out when Project Server invoked the event handler, you must check it back in by using the QueueCheckInProject method.
To use impersonation, you must know whether the user you are impersonating is a Windows Authentication user or not. Although the ContextInfo parameter passed into the event handler contains an IsWindowsUser property, I have observed some erratic behavior with this value. To avoid a call to the Resource PSI Web service, check the UserName property of the ContextInfo parameter. If it starts with a prefix associated with a custom MembershipProvider, the user is not a Windows Authentication user.
The functionality to retrieve and synchronize the active issue count is complete. However, because you are packaging this code as a SharePoint Feature, you should create a feature receiver to automate installation of the event handler to Project Server.
Procedure 14. Create an installer to register with Project Server
In Solution Explorer, add a new class named ActiveIssuesHealthInstaller.cs to the ActiveIssuesHealth project.
Add the following line to the global references section of ActiveIssuesHealthInstaller.cs.
Remove the throw new NotImplementedException() statement from all four methods.
Develop the FeatureActivated method. This method uses the Events PSI Web service to register the ActiveIssuesHealth event handler with the Project Server OnPublished event.
For more information about the Assembly class used in this procedure, see Assembly Class.
Develop the FeatureDeactivated method. This method uses the Events PSI Web service to remove the registration of the ActiveIssuesHealth event handler from the Project Server OnPublished event.
Upon completion of the ActiveIssuesHealthHandlerInstaller class, the ActiveIssuesHealth project is complete. Your remaining tasks include creating three Stsadm extensions to enable configuration of the event handler's settings, and packaging the solution for deployment.
Procedure 15. Create the Stsadm extension project and set references
Add a new project of type Class Library named ActiveIssuesHealthConfig to the solution.
Rename Class1.cs to SetSspName.cs.
Edit the properties for the ActiveIssuesHealthConfig project, enable assembly signing, and create a new keyfile.
In Solution Explorer, in the Add Reference dialog box, add a reference to Microsoft.SharePoint.dll. Following is the default file location:
%ProgramFiles%\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI\
Note
If you are developing on a 64-bit server, you should manually browse to drive\Program Files instead of using the %ProgramFiles% environment variable. Because Visual Studio 2008 is a 32-bit application running under the WOW64 emulator, %ProgramFiles% resolves to drive\Program Files (x86) in a 64-bit environment.
Add a reference to the ActiveIssuesHealth project to the ActiveIssuesHealthConfigproject.
After you have created the project, you can develop your Stsadm extensions.
Procedure 16. Develop the SetSspName Stsadm extension
Add the following lines to the global references section of SetSspName.cs.
Develop the SspName method. This method instantiates the necessary SharePoint classes, validates that the user is attempting to configure a PWA site, and passes the parameters specified on the command line to the HandlerSettings class that you created earlier, for storage in the SPPropertyBag.
Develop the ActiveStatusValue method. This method instantiates the necessary SharePoint classes, validates that the user is attempting to configure a PWA site, and passes the parameters specified on the command line to the HandlerSettings class that you created earlier, for storage in the SPPropertyBag.
Develop the TargetCustomField method. This method instantiates the necessary SharePoint classes, validates that the user is attempting to configure a PWA site, and passes the parameters specified on the command line to the HandlerSettings class that you created earlier, for storage in the SPPropertyBag.
Replace the PublicKeyToken above with the correct value for your assembly.
Save and close feature.xml.
On the Build menu, click Package Solution.
Avoiding Infinite Recursion
Project Server invokes ActiveIssuesHealth through the OnPublished event, which fires after a project publish is complete. If ActiveIssuesHealth updates and republishes the project, it causes Project Server to raise the OnPublished event for a second time, and so on.
In this article, I mitigated the risk of an infinite loop by having the CreateUpdate method return a null value when it attempts to process a project with no change in the number of active issues. This null value is passed into the UpdateProject method, which terminates in response.
When developing Project Server event handlers, you must always guard against an infinite recursion loop, because this defect can bring down a SharePoint farm in seconds.
Deploying the ActiveIssuesHealth Event Handler
To deploy the ActiveIssuesHealth event handler, you can use the VSeWSSautomatic deployment functionality, deploy with a mix of Stsadm commands and Web functionality, or use Stsadm exclusively. In this procedure, I demonstrate the second (mixed) option.
Procedure 21. Deploy ActiveIssuesHealth
Open a Command Prompt window and browse to the bin directory in the "12 Hive". Following is the default location:
c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\BIN\
Use the Stsadm addsolution operation to install the solution. For example, you would execute the following:
After the command reports a successful completion, open Internet Explorer and browse to Central Administration.
Open the Operations section. In the Global Configuration section, click Solution Management.
On the Solution Management page, in the list of installed solutions, click ActiveIssuesHealthwsp.wsp.
Click Deploy Solution.
Specify a deployment time, if desired. Otherwise, ensure that the Now option is selected, and then click OK.
This returns you to the Solution Management page. After the status column for ActiveIssuesHealthwsp.wsp displays Deployed, browse to the PWA site where you want to install ActiveIssuesHealth.
On the Site Actions menu, click Site Settings.
On the Site Settings page, in the Site Administration section, click Site features.
For ActiveIssuesHealth, click Activate.
Use the Stsadm extensions to configure the handler's settings, and then publish a test project.
Conclusion
For all of its features, the true power of Project Server is its vast capacity for extensibility and customization. Instead of merely providing an API with which developers can exchange data with the system, or attempting to cram in every conceivable feature, Microsoft built a solid architecture that enables third-party applications to inject their own business logic and other customizations into the platform with relative simplicity. This is true not only of Project Server, but also of Windows SharePoint Services 3.0 and SharePoint Server.
Additional Resources
For more information, see the following resources: