Migrating CRM Activity Entities Properly

When it comes to the migration of CRM activity entities, there are quite a few challenges lying ahead. Until they are fully addressed, you really can't say for sure that your migration has been done correctly or properly. Let's first discuss some of the facts around CRM activity entities.

• Activity is actually a special group of CRM entities, instead of just one. When you create a new entity in CRM, you can designate the entity to be an activity entity, which will inherit the standard behaviors of CRM activity entities.
  
• Each activity entity has an actual CRM entity name, such as email, phonecall, appointment, etc. However, there is also an umbrella entity that is called activitypointer, which contains a consolidated list of all activity records.
  
• CRM activity entity can contain one or more activityparty fields which are also known as party list fields. Those are a special kind of CRM fields, as CRM can store multiple party values in one single field.
• As mentioned above, CRM activityparty fields can contain multiple parties in a single field. The party can be an account, contact, lead, queue, system user record, or even just a plain email address. In the case that the party is an account, contact, lead, queue or system user record, it is essentially a lookup value, which is identified by the lookup type and then a lookup GUID value. For instance, a party can be an account with a GUID "{F95E633C-3FDC-45BA-BACA-32B9878106C7}".
  
• CRM displays activity records on the activity wall (or social wall) using the sequence based on the values in the modifiedon field, so a newer activity record appears before the older ones.
  

With that being said, the above mentioned facts naturally lead to the following two main challenges for its data migration effort.

1. For the activityparty fields, you would have to find a way to use different strategies for different lookup entities. For instance, you would typically do a full migration of account, contact, lead and queue entities, so the CRM destination component can really just take GUID values for those entities when constructing a partylist value. However, if one of the parties is a system user, then it is a totally different situation, as CRM instances do not typically share the same systemuserid (GUID) for the same CRM user. In order to migrate those system users used in activityparty fields, there is a need for some sort of lookup strategy so that we can find the user's ID in target CRM system when constructing the activityparty fields.
   
2. As we mentioned above, CRM display activitys records on the activity wall in a descending order of the modifiedon field. However this particular field is not write-able in the CRM destination component, therefore you can't bring the old modifiedon values to the new system, as CRM defaults the field to the datetime of the last write action - which is when the migration happens. The problem is, if the activity is not migrated in the right order, then everything on the activity wall can be out-of-sync from the source system. You might think this is easy, you just have to sort by modifiedon field in the source component. But the problem is, activity wall is made up of records from all activity entities, not just a single one. Sorting each individual entity would not help with the situation.
   

For the above first issue, the solution is fairly simple if you use the Text Lookup feature offered in our software. For each activityparty field, you would set it up to perform a text lookup. We have designed our software to output a JSON data structure in the CRM source component which contains PartyId, Type (the party type), and Name (the party's name). This structure makes it possible to perform a Text Lookup for any involved party entities, and the CRM destination component can use this data structure as the input without problems. As we just mentioned, for account, contact, lead, queue entities, we usually do a full migration, so that we can assume all those party IDs are already been in the target system for them, in which case we opt out from performing the Text Lookup. For systemuser entity, we will perform a lookup on the entity's primary field (which is the only available lookup option when working with activityparty fields - as the JSON structure only contains Name, no other text fields are available). The following is a typical setup for a partylist field which handles the challenge that we have just discussed. 

For the second issue, our solution is to first migrate all activity entities using Create or Upsert action in the CRM destination component. In this step, we have one data flow task for each activity entity so that we can do a full migration of all necessary fields. As you know, you can't create a CRM entity record with an inactive status. So in this step, we have excluded the statecode/statuscode fields in the mapping, because they require a separate write action nonetheless (which will later be used to update the activity records so that we can set the modifiedon field in the right sequence).

As you can see from the above screenshot, we have tried to manage the dependencies between those entities, we migrate campaignactivity entity first, then those common activity entities (phonecall, task, email, etc.), and followed by the campaignresponse entity.

After the above is done, we then perform a final update of all activity records using a new data flow task which is attached right after the above SSIS sequence container.

The following is what's inside the last data flow task shown above. It is a very simple data flow. Basically we are reading from activitypointer entity (instead of individual entities this time), and then send all records to a CRM destination component to perform an update of the statecode, statuscode fields. Note that in the FetchXML query of the source component we are sorting the results by the modifiedon field, so that we can have a sorted output for the destination component which will process the all records in a sorted order that is supposed to be the same as the source system.

The following is the screenshot of the destination component. As you can see, we are again working with the activitypointer entity, and we perform an update based on the primary key.

The following is the column mapping page of the destination component. You can see that we are actually only updating the statecode and statuscode fields in this step.

With above setup and configurations, you should be able to migrate the activity records in the right sequence.

In addition to the above two main challenges, there are also some challenges for the recurring appointment records, which have been addressed in the blog post written by my colleague - Chen Huang.

This basically summarizes the challenges around CRM activity entity migration, and the techniques that you can use in your data migration project. For your convenience, this whole migration process is demonstrated in the sample CRM migration package that we have just shared. I hope you find this useful.