It is a fairly big challenge in an ETL project when you need to migrate Dynamics 365 CE/CRM/CDS/Dataverse activity entity data due to the activityparty fields involved in the entity. We have a previous blog post where we covered the migration of activity entity records from one CDS/CRM/Dataverse instance to another, utilizing the out-of-box CDS/CRM source and destination component capabilities provided in our SSIS Integration Toolkit for Microsoft Dynamics 365 product. The process involved a very straightforward mapping from the source system to the target system, which does not require some extensive effort, since the source component output those activityparty fields in JSON format, which can be directly consumed by the downstream CDS/CRM destination component without the requirements of making further transformations or manipulations. The ETL development was straightforward with the assistance of the Text Lookup feature provided in the CRM/CDS destination component. However, there are cases where your source of migration is not actually a CDS/CRM instance but rather a database table from your legacy system, or it might be an offline CDS/CRM database that you don't have an easy way to properly attach/import back as a CDS/CRM organization. In either of such cases, you would have to develop a strategy to construct a similar activityparty JSON format so we can pass it to CDS/CRM destination components for writing purposes.
In this blog post, we will show a solution that can read data from an offline CDS/CRM database to generate the JSON structure using the SQL FOR JSON clause, which then can be sent to a CDS/CRM destination component for writing purposes.
To begin with, the following screenshot shows a sample of our input data; this is a mimic of the activitypointer entity data in D365 CDS/CRM/CE/Dataverse instance. As you can see, for one activity record, there may be multiple associated activitypointer records. Taking the “603CA9FE-B0B7-4DD1-93F6-0F3C6DD2BB28” activity record as the example, there are 2 associated parties: one account record (the first row) and one contact record (the second row).
Our objective is to construct the activityparty JSON data structure using a SQL query so that we can pass it to a CDS/CRM destination component in order to write to the partylist fields in the target CDS/CRM activity entity. Based on this requirement, we resort to the SQL FOR JSON and GROUP BY clauses to construct the JSON document, which ends up with a SQL query below. Essentially we are performing a self-join on the activitypointer entity, and each set of child rows is formatted as a JSON array before it is constructed as a JSON document.
SELECT activityid, (SELECT regardingobjectid AS PartyId, regardingobjecttypecode AS Type FROM activitypointer a WHERE a.activityid=b.activityid FOR JSON PATH ) AS activityparty FROM activitypointer b GROUP BY activityid
The above SQL query can be used in an OLE DB source, ADO.NET source, or our Premium ADO.NET source component to emit the proper result. In order to validate the source component's output, we may turn on Data Viewer after the source component so that we can see the data flow through the SSIS pipeline. The following screenshot shows the desired JSON output value after the source component:
Once we have the JSON activityparty ready, we can simply send the data to a CRM/CDS destination component's activityparty fields for writing. In the destination component, you can leverage the Text Lookup feature for those party list fields as necessary. What we have accomplished is an almost identical activity entity migration experience like a CDS/CRM source to a CDS/CRM destination migration. This strategy can be used for legacy db to CDS/CRM/Dataverse migration as well.
We hope this has helped. Please feel free to let us know if you have any further comments or suggestions.