Multi-entity data migration from Dynamics 365

Recently I ran into a task where I needed to migrate data from Dynamics 365 into SQL Azure DB. Piece of cake, I will just use the SSIS enhanced with the KingswaySoft SSIS toolkit for Dynamics. Well, there was one a bit of a challenge which is due to Dynamics data model for lead entity. So, let me specify the scenario in a bit more detail.

In high level, this scenario was such that leads stored in Dynamics 365 should be migrated over to a custom DB hosted in SQL Azure service. However, due to the way this organization had used Dynamics, there were three types of leads in CRM when considering the way how leads were linked to customer entities:

Leads which were not linked to customer entities at all
Leads which were linked directly to account entity
Leads which were linked to contact (and then contacts had parent customer as account)

In the SQL Azure DB, the lead table model was such that only company information or CRM account GUID can be saved. So, there was no place to save the contact information of the lead. So somehow in the SSIS package, I would need to fetch the account information to be stored to SQL for the third type of leads in the above list. It is not directly available in the SSIS for mapping when lead entity records are fetched from CRM.

Based on this information I started to plan the SSIS migration package model. Should there be three separate packages, one for each scenario mentioned above or one package in which there would be logic for handling the above three scenarios. For you SSIS gurus out there, this would probably be a no-brainer but for me, it took a bit of an effort to clarify this situation. The way I ended up handling this was within one SSIS package containing conditional split control and merge join control handling the most challenging phase:

So here is the data flow how it goes from top to down:

KingswaySoft Dynamics CRM source component queries leads from Dynamics 365
Within the conditional split control, I use the lead.customeridtype attribute to check the type of the customer saved to each lead:
Lead data is sent to three different data flow paths depending from the customeridtype information.
The easiest ones are the scenarios where lead does not have any customer entity or lead is linked to account entity. From these two, I just send the data over to OLE DB destination control where I map the CRM lead attributes to proper SQL Azure DB fields. For the leads not linked to any customer, the lead.companyname field value is used for the customer info in SQL Azure. For the leads linked to account, the account.name is used.
But then the challenging part: leads which are linked to contact, I should somehow fetch the contact’s parent account but it is not directly available in the lead.customerid attribute when that contains a contact. So what I ended up doing was that I used the SSIS merge join control in which I did a join between the lead.customerid (GUID) and contactid (GUID) and then fetched the contact.parentcustomerid attribute and sent it as an output to the OLE DB destination control. Naturally I needed to query all the CRM contacts and for that I used again the KingswaySoft Dynamics CRM source control. Here are the details of the join control logic:

The rest was just the normal day in the office and mapping the proper fields, pressing the famous Ctrl + F5, fine tuning my chair to proper position, having a sip of griin dii with mind (honey on the side) and watching the data flowing as nicely as crystal clear water in the Näätämö river.

So, with these words, I wish all of you sunny and peaceful moments during the summer. Have fun and live strong!