In this post I will show you how to use Dataflows as a data migration tool to do initial data load/sync of the accounts from the source system to the target. My initial plan was to do both Account and Contact migration in this post as well as talking about how to do an incremental/delta sync. But this post is long enough just talking about using dataflows for data migration initial sync. This will therefore be at least three-part series and in part two I will outline how to migrate the contacts and keeping the correct relationship.
I have been working on a project where we are migrating one companies Dynamics 365 CRM application into a pre-existing Dynamics 365 CRM application at another company. Both Dynamics 365 applications are on the latest version, they are both cloud based and both use Dataverse for business data storage. The biggest area of work in this project is the data migration from source to target. The amount of data is fairly small with some 3.000 accounts and the whole history is only three years so we decided to do some tests and migrate the whole history.
I am not an expert on Dataflows or Power Query and there might be other better ways to use Dataflows for this purpose. If you have other and better ideas then please share them with us so we can all learn from them.
My goal with this blog is to introduce this option and get you started building your own Dataflows.
Using the Dataflows
For this example, I will showcase how I created two different Dataflows, one for the initial sync and another for delta/incremental sync.
Preparation for the target environment to handle row updates
Before you start creating the dataflows you need to add an alternative key to the table you are going to migrate the data to. In this example that is the Account table, I simply added a new text field to the Account table and then added it as an alternative key.
- For the Account table select the “Columns” tab
- Click on the “+ Add column” button to add a new field/column
And then add it as a key to the table
- Select the “Keys“ tab
- Click the “+ Add key” and select your newly created field/column
If we don’t do this then we will get errors when we run the delta sync and want to update rows in the target system. The dataflow will give us an error saying that we are adding a duplicate of a record that already exists in the table. This will be more clear when we get to the dataflow for the delta sync.
Create a dataflow to sync all accounts
In the target environment i.e. the environment that I want the migrated data loaded to, I create the two dataflows using the Power Apps maker studio, https://make.powerapps.com/
- Logon to the Power Apps maker studio
- Expand the “Dataverse” menu in the left navigation
- Select the “Dataflows” menu item
- Click the “New Dataflow” and select to create one “Start from blank”.
In the dialog that opens then
- Give the dataflow a descriptive name
- Click “Create”
From the Power Query – Choose data source wizard
- Select the “Power Platform” Tab
- Select “Dataverse” as your data source
In the next step of wizard
- Select “Create new connection” from the Connection dropdown box
- Give the new connection a name
- Select “Organizational account” for the Authentication kind dropdown box
- Click the “Sign in” button to sign in with an account that has administrator access to your source system.
- Click “Next”
In the next step “Choose your data”
- Select your source environment
- If prompted to “Configure connection” then do so by clicking the button and sign in with an administrator account for the source environment.
- Click “Next”
When connection has been established select your source table
- Select your source table, in this case I have selected “Account”
- Click “Transform data”
Select columns, transform values, replace values and other functionality is available in this step of the wizard. If you want to query the data you would do it here, for example to only select active accounts. In this example I am not doing any filtering, but I choose all columns from the Account table and if you have many custom fields you would select them here and then skip fields that aren’t present in the target environment.
- Click the “Choose columns” button and make sure that “Select all” is marked. As stated above you need to adjust this step to your specific scenario and the field list you want to migrate the data for.
- Click “Next” to go to the field mapping step of the wizard.
The final step in the wizard is mapping the fields from the source table to the target table
- Select the “Account” table under “Queries”
- Choose “Load to existing table” from the “Load settings”
- Under the “Destination table” select the “Account” table from the dropdown list
- Click the “Auto map” button
- Find the field you created for the alternative key in the preparation step in the beginning and map it to the “accountid” field
- Finally publish the dataflow
After the dataflow has been published it will automatically refresh and migrate the data from the source to your target
- The dataflow has been published
- The refresh/sync has been finished
At this stage your new data from the source system has been migrated to the target system without any coding, stored procedures, or any fancy tools. This has all been done directly in the Dataverse itself across two different tenants.
In part two of this blog series, I will cover how to migrate the contacts for the migrated accounts and re-map the contacts to the correct account. We need to do that sine the migrated accounts get their own GUID ids in the target system when they are created so the migrated account ID on the contacts will be incorrect.