Experiences using EPMSync 1.0

Feb 21, 2008 at 3:52 PM

Overview

As part of implementing bidirectional synch capability, a client used the runtime version of EPMSync to push Microsoft Office Portfolio Server 2007 (PPS) owned attributes to Microsoft Office Project Server 2007 project custom fields. Here is a rundown of experiences and issues faced.

The client environment consists of:
• a front-end PPS server
• load balanced MOPS servers (2 node) in a web farm
• SQL Server containing PPS databases
• SQL Server containing MOPS databases

EPMSync sends changed PPS attribute values to MOPS. This is originated in a custom SQL trigger that processes changes to the sfATTRIBUTE_VALUES table in PPS and places them into a custom table called esQUEUE_ATTRIBUTE_EVENTS.

Batch processing

The original issue faced was that we needed for PPS to own particular attribute values. Since EPMSync sends over only changed PPS data to MOPS, if those same field values are later changed in MOPS, EPMSync would not enforce that PPS ownership. Thus we created an add-on for EPMSync (we call it EPMSyncBatch) that would send all EPMSync-mapped attribute values for all PPS projects en masse to EPMSync for processing. This add-on was written as a scheduled Windows service to run once a day. This way if PPS-owned values changed in MOPS, they would be overwritten during the daily update. Another benefit of this service is it brings that capability of allowing initial population of PPS values into MOPS.

EPMSync issues

Verification of Project Server

We installed the EPMSync runtime onto the PPS server. When we ran it for the first time, it would never initiate processing with MOPS. Analysis of the EPMSync log files unveiled a timeout issue with EPMSync attempting to verify if Project Server was running. I downloaded the code from CodePlex and traced it to the VerifyPWA routine. It was trying a web request against the base project server url. While this might have worked on a single machine install of Project Server, it did not work against a load balance environment from another server. I created a test Windows app to verify this. I therefore changed the EPMSync code to perform a network ping of the base server extracted from the project server url (with more time, I probably would have written something to test the presence of an actual running Project Server instance).

Project List Load

Once the verification worked, we quickly ran into another issue. EPMSync queries MOPS for a complete list of projects (loaded by type in order of Normal, Master, Activity Plans, and Inserted projects) and adds them to a dictionary object, using project name as the key. EPMSync kept throwing duplicate key exceptions on an Activity Plan during this load. At first I suspected an issue with name collision on an Inserted project with a Normal project but this was not the case. The client had no Master plans, Activity Plans, or Inserted projects. Upon analysis of the ReadProjectStatus call in the PSI I found that if the database held no data for a certain type and that type was requested in a ReadProjectStatus call, then the ProjectDataSet still returned a blank row. Since EPMSync processed the results in a foreach loop, it was adding a blank string to the dictionary object. The blank row from the Master projects load successfully added but the next type (Activity Plan) failed with the duplicate key exception. I verified this by adding an Activity Plan to the system and the duplicate key error began reporting for Inserted projects. I fixed this in EPMSync by only adding projects to the dictionary object if the project name had a length over zero. The EPMSync Configuration Editor has a similar routine used to load the project list to define Excluded Projects and I made the same change there. While I was in that code, I also enhanced the selection lists to allow for multiple project selection for exclusion/inclusion.

New MOPS Custom Field error

After getting this to work, EPMSync would begin processing but would fail in the case that a MOPS project did not yet have a value defined for mapped custom field. I isolated this to the PSI requirement that when creating a new custom field row for a project that the correct MD_PROP_TYPE_ENUM must be set on the row when the value is applied. I had run into this on another integration project (CRM-to-MOPS). Rather than performing a look up to the CustomFields web service to determine the data type byte value, I found where EPMSync had already retrieved this elsewhere and stored it in _projectCustomFieldList. I changed the UpdateCustomFieldValue routine to check if MD_PROP_TYPE_ENUM was null then applied the correct value from _projectCustomFieldList.

Summary

After all of these issues were addressed, EPMSync and my new add-on service EPMSyncBatch correctly transferred attribute values from PPS to MOPS. However, now I’m sitting on my own updated version of EPMSync with the responsibility to maintain it. I will still need to get permission to release the updated EPMSync. If I can obtain that, all my code changes have been documented with my name, company and year.

I must mention that having the EPMSync source code was a huge benefit to getting a working solution in short order. I did find elegant code in EPMSync and it does provide an in-depth set of examples of interacting with the MOPS PSI.

Thanks,
Paul Congdon
Milestone Consulting Group, Inc.

Mar 19, 2009 at 11:48 AM
Hi Paul,

I have an environment I desparately want to get bi-directional syncing working with EPMsync.  Mainly with syncing up from Project Server formula based data and pushing down from Portfolio Server, List, text and numerical field data.  Effectively I want to not use Project Server Gateway Template mapping as we encounter issues when Mass Exporting Projects with either Formula Based Enterprise Fields in Project Server, or if some of the mapped Ent Fields are mandatory.

I have had trouble even getting EPM Sync to work.  Have managed to modify the config so that 0 fields are mapped, but am still getting issues I think due to the Account ID.  And I have no idea what Account ID to use and where to find it....

Is the work you have done designed to overcome some of these issues?  If so I would welcome a chance to talk further and see how I could leverage your knowledge here.  I am from the business side and not from development background.

Thanks in advance

David Morrison
Mar 19, 2009 at 3:59 PM
Hi David,

To gain bi-directional syncing you have to use two separate pieces of integration software:
  1. PPS to Project Server  - EPMSync  
  2. Project Server to PPS - PPS Project Server Gateway functionality

Note: you should not have the same field mapped in both pieces of integration software as this can cause a never-ending transfer of changed values back and forth between PPS and Project Server.


EPMSync was designed to transfer only individual PPS field values to Project Server when the value of a mapped field changes in PPS.  My add-on, called EPMSyncBatch, performs a batch transfer of all project mapped values from PPS to Project Server.  It's main purpose is to ensure that the values of EPMSync-mapped PPS fields are maintained in Project Server.  For example, if EPMSync detects a change in a PPS field for a project it will send it to Project Server.  However, a Project Manager could later use Project Professional to modify the value in Project Server.  This becomes a problem if the business expects those values to reflect what is set in PPS.  Therefore, my add-on performs a periodic update (en masse) of all values for mapped fields to ensure the Project Server values reflect the current PPS value.

 

With your situation, you should never map into formula (or calculated) fields as these are essentially read-only (the formula results are the values that are placed into these fields).  You could take a few courses of action:
  1. Set up alternate Ent level fields that are not formula-based and push your sync'd values into these
  2. Remove the formulas on the fields (carefully consider this option before doing so)

With #1, you then either:

  • modify the formula in the original field to check the sync'd field for a value, if a value exist use it, otherwise proceed with the regular formula.
  • modify whatever is referencing the formula field (views, filters, another field, etc) to use your sync'd field values instead

With #2, you are depending entirely on the PPS values for these fields rather than the formula.  Be aware, with the out-of-the-box EPMSync only the values changed after EPMSync is installed are pushed down from PPS to Project Server.  Therefore you would not get an initial population of the fields in Project Server unless you manually change the value of every mapped field over in PPS.  This was one of the benefits of my EPMSyncBatch add-on.  It automates this.


 

To address your other issue of getting EPMSync to work. I encountered several issues with the CodePlex 1.0 Version of EPMSync (see my posting above).  It was originally written inside the Microsoft PPS-Project Server demo VM (virtual machine) and as such was not well tested for an enterprise cross server implementation of these products.  Since I was hired by a client to write the EPMSyncBatch to complement EPMSync it fell to me to fix the EPMSync so I could get my own add-on to function.  However that created secondary issues in that I was not allowed to post to CodePlex my updated version of EPMSync that worked across an enterprise deployment.  I can follow up with this to see if the mindset has changed.

If you need to contact me directly, use my codeplex id @milestoneconsultinggroup.com

Thanks,
Paul Congdon
Milestone Consulting Group, Inc.



Mar 22, 2009 at 8:20 AM

Hi Paul,

Thank you for your response.  One last question if that is OK:

Upon installing EPM Sync, what actions do you need to take to get it working? I have found out that it is originally set up for the Microsoft demo environment and that modification to the config is where you change this – have managed to do this OK (I think!).   I still have issues, possibly related to the Account ID (defaults to 104)?  If so how to find the Account ID that should be used?  Welcome any thoughts or ideas you may have here.


Kind regards


David

From: pcongdon [mailto:notifications@codeplex.com]
Sent: Friday, 20 March 2009 3:59 a.m.
To: David Morrison
Subject: Re: Experiences using EPMSync 1.0 [EPMSync:22597]

From: pcongdon

Hi David,

To gain bi-directional syncing you have to use two separate pieces of integration software:

1. PPS to Project Server - EPMSync

2. Project Server to PPS - PPS Project Server Gateway functionality

Note: you should not have the same field mapped in both pieces of integration software as this can cause a never-ending transfer of changed values back and forth between PPS and Project Server.


EPMSync was designed to transfer only individual PPS field values to Project Server when the value of a mapped field changes in PPS. My add-on, called EPMSyncBatch, performs a batch transfer of all project mapped values from PPS to Project Server. It's main purpose is to ensure that the values of EPMSync-mapped PPS fields are maintained in Project Server. For example, if EPMSync detects a change in a PPS field for a project it will send it to Project Server. However, a Project Manager could later use Project Professional to modify the value in Project Server. This becomes a problem if the business expects those values to reflect what is set in PPS. Therefore, my add-on performs a periodic update (en masse) of all values for mapped fields to ensure the Project Server values reflect the current PPS value.


With your situation, you should never map into formula (or calculated) fields as these are essentially read-only (the formula results are the values that are placed into these fields). You could take a few courses of action:

1. Set up alternate Ent level fields that are not formula-based and push your sync'd values into these

2. Remove the formulas on the fields (carefully consider this option before doing so)

With #1, you then either:

  • modify the formula in the original field to check the sync'd field for a value, if a value exist use it, otherwise proceed with the regular formula.
  • modify whatever is referencing the formula field (views, filters, another field, etc) to use your sync'd field values instead

With #2, you are depending entirely on the PPS values for these fields rather than the formula. Be aware, with the out-of-the-box EPMSync only the values changed after EPMSync is installed are pushed down from PPS to Project Server. Therefore you would not get an initial population of the fields in Project Server unless you manually change the value of every mapped field over in PPS. This was one of the benefits of my EPMSyncBatch add-on. It automates this.


To address your other issue of getting EPMSync to work. I encountered several issues with the CodePlex 1.0 Version of EPMSync (see my posting above). It was originally written inside the Microsoft PPS-Project Server demo VM (virtual machine) and as such was not well tested for an enterprise cross server implementation of these products. Since I was hired by a client to write the EPMSyncBatch to complement EPMSync it fell to me to fix the EPMSync so I could get my own add-on to function. However that created secondary issues in that I was not allowed to post to CodePlex my updated version of EPMSync that worked across an enterprise deployment. I can follow up with this to see if the mindset has changed.

If you need to contact me directly, use my codeplex id @milestoneconsultinggroup.com

Thanks,
Paul Congdon
Milestone Consulting Group, Inc.


Read the full discussion online.

To add a post to this discussion, reply to this email (EPMSync@discussions.codeplex.com)

To start a new discussion for this project, email EPMSync@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com

Click here to report this email as spam.



This message has been scanned for viruses by BlackSpider MailControl

Mar 22, 2009 at 4:00 PM

The Account Id would be the account id assigned the administrative account in PPS.  This is needed so that EPMSync has the appropriate level of permissions to read any changes to data in PPS.

From: DavidMorrisonNZ [mailto:notifications@codeplex.com]
Sent: Sunday, March 22, 2009 2:21 AM
To: Paul Congdon
Subject: Re: Experiences using EPMSync 1.0 [EPMSync:22597]

From: DavidMorrisonNZ

Hi Paul,

Thank you for your response. One last question if that is OK:

Upon installing EPM Sync, what actions do you need to take to get it working? I have found out that it is originally set up for the Microsoft demo environment and that modification to the config is where you change this – have managed to do this OK (I think!). I still have issues, possibly related to the Account ID (defaults to 104)? If so how to find the Account ID that should be used? Welcome any thoughts or ideas you may have here.


Kind regards


David

From: pcongdon [mailto:notifications@codeplex.com]
Sent: Friday, 20 March 2009 3:59 a.m.
To: David Morrison
Subject: Re: Experiences using EPMSync 1.0 [EPMSync:22597]

From: pcongdon

Hi David,

To gain bi-directional syncing you have to use two separate pieces of integration software:

1. PPS to Project Server - EPMSync

2. Project Server to PPS - PPS Project Server Gateway functionality

Note: you should not have the same field mapped in both pieces of integration software as this can cause a never-ending transfer of changed values back and forth between PPS and Project Server.


EPMSync was designed to transfer only individual PPS field values to Project Server when the value of a mapped field changes in PPS. My add-on, called EPMSyncBatch, performs a batch transfer of all project mapped values from PPS to Project Server. It's main purpose is to ensure that the values of EPMSync-mapped PPS fields are maintained in Project Server. For example, if EPMSync detects a change in a PPS field for a project it will send it to Project Server. However, a Project Manager could later use Project Professional to modify the value in Project Server. This becomes a problem if the business expects those values to reflect what is set in PPS. Therefore, my add-on performs a periodic update (en masse) of all values for mapped fields to ensure the Project Server values reflect the current PPS value.


With your situation, you should never map into formula (or calculated) fields as these are essentially read-only (the formula results are the values that are placed into these fields). You could take a few courses of action:

1. Set up alternate Ent level fields that are not formula-based and push your sync'd values into these

2. Remove the formulas on the fields (carefully consider this option before doing so)

With #1, you then either:

  • modify the formula in the original field to check the sync'd field for a value, if a value exist use it, otherwise proceed with the regular formula.
  • modify whatever is referencing the formula field (views, filters, another field, etc) to use your sync'd field values instead

With #2, you are depending entirely on the PPS values for these fields rather than the formula. Be aware, with the out-of-the-box EPMSync only the values changed after EPMSync is installed are pushed down from PPS to Project Server. Therefore you would not get an initial population of the fields in Project Server unless you manually change the value of every mapped field over in PPS. This was one of the benefits of my EPMSyncBatch add-on. It automates this.


To address your other issue of getting EPMSync to work. I encountered several issues with the CodePlex 1.0 Version of EPMSync (see my posting above). It was originally written inside the Microsoft PPS-Project Server demo VM (virtual machine) and as such was not well tested for an enterprise cross server implementation of these products. Since I was hired by a client to write the EPMSyncBatch to complement EPMSync it fell to me to fix the EPMSync so I could get my own add-on to function. However that created secondary issues in that I was not allowed to post to CodePlex my updated version of EPMSync that worked across an enterprise deployment. I can follow up with this to see if the mindset has changed.

If you need to contact me directly, use my codeplex id @milestoneconsultinggroup.com

Thanks,
Paul Congdon
Milestone Consulting Group, Inc.

Read the full discussion online.

To add a post to this discussion, reply to this email (EPMSync@discussions.codeplex.com)

To start a new discussion for this project, email EPMSync@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com

Click here to report this email as spam.

This message has been scanned for viruses by BlackSpider MailControl

Read the full discussion online.

To add a post to this discussion, reply to this email (EPMSync@discussions.codeplex.com)

To start a new discussion for this project, email EPMSync@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com