We recently completed a project where we had to migrate Dataverse data from one environment to another. Usually, this can be achieved in multiple ways using tools like KingswaySoft, Data Migration Tool, custom console implementing Dataverse SDK, etc. However, our scenario was a little more complicated, as we had to also migrate Project for the web with its data residing in Scheduling entities in Dataverse. Currently, there is only one way to programmatical create, update, and delete operations with Scheduling entities, and that is via Project schedule APIs. There are a couple of excellent posts that dive into the inner workings of Project Scheduling Service and the usage of Project schedule APIs by Antti (Day to Day Dynamics 365) and Joe (The CRM Chap) so I won’t repeat that content here. The primary focus of this post is around sharing the lessons I learned and issues that were resolved with the help of wonderful individuals in the Microsoft Product Team. I hope this will help anyone who might need to perform similar duties in the future.
Our Scenario
When we started to implement Project for the web (P4W) for our model-driven app (MDA) implementation, we only had the option to deploy on the tenant’s default environment. This resulted in spinning up two extra tenants to achieve application lifecycle management (i.e. DEV/TEST) which was not ideal as it added unnecessary complexity and additional cost to developing solutions for P4W. In early 2021, all this changed when Microsoft introduced the ability to deploy P4W into Sandbox and Production Dataverse environments. When this news was announced, we already had our P4W MDA running at a production capacity on the default environment, along with a separate production environment to host our other MDAs. We knew that it would be crucial for us to consolidate the two production environments when the option to do so was made available. When the public preview of the Project schedule APIs was announced in early 2021 (around April 2021 I believe), we planned for our data migration.
It was not an easy exercise, initially with Project schedule APIs still in preview, we had to stop/start multiple times due to certain API operations not working as expected, as well as having to look for alternative ways to implement the gaps in Project schedule APIs using RPA.
As for any data migration, data parity between environments is crucial. Migrating data for the Project Task scheduling entity (msdyn_projecttask) was most challenging for us so that is what we are going to focus on in this post.
Note: Some of the lessons learned were due to our particular configuration of P4W, where if done differently might not have been an issue.
#1. The Timezone Code on the Calendar Rule Matters!
For any given project, the calendar template must be specified. Whenever a project is saved with a new reference of a calendar template, a copy of the calendar template’s calendar along with its calendar rule is associated with the project. The easiest way to find the calendar id for a project is via the Dataverse Web API:
/api/data/v9.2/msdyn_projects(<project-guid>)?$select=msdyn_calendarid
Now, the gotcha. As specified in this Microsoft documentation, the calendar rule is created based on the resource’s working hours assigned on the calendar template. When the working hour of the resource is updated or recreated, this change does NOT propagate to the calendar template’s calendar nor to the copies of the calendar associated with the projects.
When we started to migrate project tasks, we noticed straight away that the start and finish dates of the tasks weren’t migrating correctly. Without the above knowledge, at first, we thought the problem was due to the timezone of the service account performing the Project schedule API operations, or the Microsoft Portfolios user that P4W uses to create the project tasks. Later we realised that the calendar template that was being used to create the projects in the source environment had a different timezone code specified in its calendar rule compared to the target environment!
To retrieve the timezone code for a calendar rule, use the following query:
/api/data/v9.2/calendars(<calendar-guid>)?$expand=calendar_calendar_rules($select=timezonecode)
In the source environment, we had the timezone code 92 ((GMT) Coordinated Universal Time) but in the target environment, we had the timezone code 255 ((GMT+10:00) Canberra, Melbourne, Sydney). It was more confusing in our case because the resource assigned to the calendar template in the source environment had his work hour timezone set to (GMT+10:00) Canberra, Melbourne, Sydney 🤯. So how is it then all the project calendars have timezone code 92? Does it mean this resource initially had his work hour timezone set to GMT but later had it updated to GMT+10:00?
For us, we didn’t create a new calendar template to use with the projects but we were using the default calendar template called Default Work Template. This default calendar template is special because the system will re-create a new calendar template with the same name when one doesn’t match the name or one doesn’t exist. When a bookable resource is created, the system uses the default calendar template to automatically create the working hours for the resource. The secret lies in how this default calendar template is created: 1) implicitly triggered via some process like creating resource booking, 2) created manually, or 3) created when P4W was installed. In any case, if the resource is not assigned on the default calendar template, a copy of the executing user’s calendar will be associated with the default calendar template.
To speculate on our scenario further, I believe that the user who installed and configured P4W in our source environment had a personal timezone set to UTC. This would, in effect, have created a calendar for the default work template with a calendar rule set to timezone code 92. Any new booking resources created at this point will have a calendar with a calendar rule with timezone code 92 as well. I’m guessing sometime early on the default calendar template must have been updated with one of these resources as its resource. That explains why the resource’s work hour update to GMT+10:00 later did not have an effect on the calendar template’s calendar rule 🤔.
Long story short, make sure that the calendar rule and its timezone for the calendar template are the same on both the source and the target environment before migrating!
#2. (Start Date + Duration) OR (Finish Date + Duration) When Creating Project Tasks
When migrating project tasks, it seemed logical at first to include both the start and finish dates along with the duration when creating the msdyn_PssCreateV1 request. Later we were told that we shouldn’t do this. Depending on the project task type (i.e. milestone) and the time specified on those dates (i.e. 5 pm) can cause recalculation of the dates as it gets migrated. For example, we had the following dates:
You can see the payload sent to Project Scheduling Service (PSS) by opening up the OperationSet Details for this request. You can learn more about the Project scheduling logs here.
Several things to note here:
- Both the start and finish dates are being sent in the request along with the duration
- This is a milestone task (i.e. duration = 0)
- The time component for these dates is both set to 5 pm (i.e. end-of-day in the calendar working hour)
When we presented this issue to the Microsoft Product Team, they said:
- For any project task creation, we only need to send one of the dates (either start or finish) with the duration
- If the time component of the start date is set to 5 pm, it will roll over to the next working day. This is what we are seeing in project task #16. 16th October 2020 is Friday, therefore rolled over to 19th October 2020 which is Monday, the next working day.
To work out which date to include, we applied the following rules:
- If [start date hour == 5 pm] and [finish date hour == 5pm] and [is milestone task] => Include finish date
- Else If [start date hour == 9 am] and [finish date hour == 9 am] and [is milestone task] => Include start date
- Else => Include start date
Note: All our project calendars are set to UTC timezone. Therefore it made it easier for us to compare the time without any conversion. There were occurances where time was not set to neither 9 am or 5 pm. These had to be manipulated in the code prior to migrating the data.
#3. Project Task Dates are Still Different, Why?
This relates to what we have covered in #2. Check if the start or finish dates of the project task are not set on a weekend. We noticed a few occurrences of this in our environment. If set on the weekend, it will roll over to Monday if the project calendar’s working hours are from Monday to Friday.
Also, check whether the project task has any dates assigned. Currently, the scheduling API doesn’t allow project tasks with no dates to be created. Even when no date or duration attributes are set in the create request, it will calculate the dates using the previous tasks and set a duration of 1. We have explicitly tried to set the start date as null but it produced the following error:
System.ServiceModel.FaultException`1[Microsoft.Xrm.Sdk.OrganizationServiceFault]: Null object cannot be converted to a value type.
#4. The Duration and Finish Dates May Change When Resource Assignment User is no Longer Available
As we were migrating project team members we noticed that there were users who were no longer with the business. This meant that the resource assignment for a project task may not get the full resource assignment that it had in the source environment:
As you can see, the value of the duration and finish date has changed for project tasks #75, #76 and #78. This is because the number of resource assignments assigned to the project task cannot fulfil the specified effort in the original duration. Let’s take #75 for example:
- It initially had 4 resource assignments but is now reduced to 3 resource assignments
- It requires 544 hours of effort to complete the task
- Source duration calculation = 544 hr / 4 / 8 hr = 17 days
- Target duration calculation = 544 hr / 3 / 8 hr = 22.67 days
- Therefore, the finish date on the project task changed from 15/1/2021 => 25/1/2021
Unsurprisingly, for project task #79 the only resource assigned is not available anymore so the duration stays the same.
This is not the desired effect in a migration scenario as the data should match in both environments, especially dates. Especially when the project has been completed. To keep the data the same, we had to update the project tasks after migrating the resource assignments. Just like project task creation, only one date along with duration must be included in the update request.
Note: There is currently a bug that stalls the execution of the msdyn_ExecuteOperationSetV1 request when the OperationSet does not include any resulting PSS changes. There is a fix getting released for this so for now just make sure to check the update is necessary.
#5. Don’t Set Project Task Progress For Summary Task
A bit of history, the % completed (msdyn_progress) for project task was initially allowed only on the update request for P4W. Even when this ability was made available, we decided to keep the logic on update requests. To update the % completed, we iterated over all the project tasks with % completed > 0 and created update requests for those tasks. When we applied the update, we noticed strange date changes on the non-summary tasks. What we didn’t realise was that updating summary tasks (i.e. msdyn_summary == 1) with the % completed value has side-effects. The lesson here is to always exclude summary tasks for the % completed updates!
#6. Always Create Project Tasks in Order
This is an obvious one but when migrating project tasks, make sure to order them by the display sequence (msdyn_displaysequence) and create msdyn_PssCreateV1 request in that order.
Happy migration! Hope you enjoyed the post, let me know your thoughts in the comments below or on any of my contact channels 😎
Photo by Eric Rothermel on Unsplash.
