Using Data Flows to Migrate Data Between Dynamics 365

Data Flows is one good feature that I feel is underrated and doesn't seem to be used widely for the feature set it provides. Until recently I have used alternative tools like SSIS with KWS connector or Data Transporter from the XrmToolBox for some of the use cases like performing a one time migration, migrating data between Dynamics 365  instances etc.

In this post, we will be migrating data from one Dynamics 365 instance to another using OData feed and the Dataverse connector to see when one might be better over the another.

Using OData to Migrate Data between Dynamics 365

For this use-case, I am going to keep it simple and use the Table Contact as our source data that needs to be migrated into another instance. I have already created a view as below in my instance.

Next, I am going to download the FetchXML for this view and open it in FetchXML Builder in XrmToolBox. This step is optional and I'm primarily using it for converting the FetchXMl into an OData URL as below. If you can build the OData query easily, you can skip this step.

Here is the OData URL

https://ORG_NAME.crm.dynamics.com/api/data/v9.2/contacts?$select=fullname,entityimage_url,emailaddress1,contactid,firstname,lastname,gendercode,birthdate&$filter=(statecode eq 0 and Microsoft.Dynamics.CRM.Last7Days(PropertyName='createdon'))&$orderby=fullname asc

Now that we have the OData url, let's continue to Dataflows in make.powerapps.com and create a new Dataflow. Make sure to set the environment to the Target environment where you want to load the data. In our previous step we generated the OData URL from our source environment.

New Dataflow

Select OData

Specify the OData URL, connect to the source and click Next

This should now open PowerQuery similar to PowerBI where we can make needed transformations before loading our data. I'm going to leave it as is.

Since, we are using contact Table we would make the changes as below and click Next. Make sure the mapping is correct.

We are now at the last step of our Dataflow. We can now configure our Dataflow to refresh on a schedule basis as below or just run it manually.

I am going to perform the refresh manually for now and publish the data flow.

Once the data flow is published, we should see it complete after a short time if everything goes as expected.

If we now check our data we should see the two test records being created.

Using Dataverse Connector to Migrate Data between Dynamics 365

Now let's look at accomplishing the same using the Dataverse connector. Some of the steps are the same between the two, so I will keep this short.

Let's create a new Data Flow and select the Dataverse connector.

Create a new connection to the source environment and Sign In. Do not include "https://" as it only needs the domain.

Select Contact Table and the data should load shortly after. The loading part is one of the pain points when using a Dataverse connector when you are working on a huge table that has millions of rows and a large number of columns like contact.

Select the columns we need to migrate and click OK. In this case I have chosen as below to match to the previous usage of the OData connector.

Note that I have selected createdon at this point because we need it for filtering contacts created in the last 7 days.

Now let's add the filter for the CreatedOn column

Specify last 7 days and click OK.

Now our data should be filtered and we should see our test contacts as below

Since we no longer need the createdon, we can remove this column. Now our data is ready for migration. Click Next.

We will now map the columns to the Contact table and Publish our Dataflow.

Once the Dataflow is published, we should now see our contacts being migrated to the target environment. We can confirm by first looking at the Refresh history and then validating it in CRM.

Summary

Now that we have seen both the approaches, here are the differences:

• In the OData approach we mostly do not have to perform any transformations using PowerQuery if we optimize our query ahead.
• Dataverse connector is the standard connector and is easier to use compared to OData but depending on the size of the Table, the processing might be slower compared to OData.
• Dataverse connector also allows Relationships when creating the connection to be included making it easier to work compared to OData in some cases.

Which approach would you prefer? Let me know in the comments below.