Recently I did a data migration from Microsoft Dynamics CRM On-Premise to CRM Online using the SSIS integration toolkit. This tool is useful for migrating data between CRM instances without needing to export everything into Excel spread-sheets or write a console application.
While migrating opportunities between my CRM instances, I had to migrate several closed opportunities and because these were closed an “Opportunity Close” record was created automatically for them. However, this record did not contain an Actual End Date which is some information I needed to keep. So I wanted to remove these records from the destination system and copy my existing ones over.
Here’s what I did:
First we want to remove only those Opportunity Close records that don’t contain an Actual End Date value (in the destination system). In the toolkit you are given the option of either using an entity or a FetchXML query as the source type. Use FetchXML as you can specify the selection criteria to take only those records which don’t contain data for their Actual End Date value.
<fetch mapping='logical'> <entity name='opportunityclose'> <attribute name="subject" /> <filter type="and"> <condition attribute="actualend" operator="null" /> </filter> </entity> </fetch>
We are going to delete these entities so use the Delete action in the destination.
NOTE: When performing the Delete action, the Connection Managers we configure need to be the same. This is because we are not moving data from one system to another, but editing the content that’s already there.
Next we migrate the correct data, this time using entity as the Source Type. The Connection Managers in this step will need to be different, that is, one pointing to the source system and the other pointing to the destination system.
This action will be performed as a Create. This is because we know that there is no matching Opportunity Record in the destination system, and also Opportunity Close records cannot be updated.
Incorporate these steps into your process the next time you need to migrate closed Opportunities from one CRM system to another. This will ensure that your data is accurate.