Versioning config data in D365/Power Apps - beyond the Configuration Migration Tool

Config data can be important - but handling it is hard!

D365/Power Platform projects often use configuration data to drive them. This is data that is stored in Dataverse tables that define/alter the behaviour of the system. For D365, that can be things like business units and SLAs.

This config data can be as important as any of the other customisations used in the system - If it’s not 100% correctly in place in a given environment, that environment will behave differently. The effects of this not being the case can be a massive time sink, with your team spending hours trying to understand why test environments are misbehaving, or a go-live risk with things working perfectly in your test environment and then failing in production!

See my previous post for general thoughts about how different approaches to handling such processes compare.

So how do we solve that - the CMT?

The tables where this data is stored are often not solution-aware (cannot be added to a solution) so it’s not possible to version and deploy this along with the rest of your solution.

Microsoft provide a tool called the Configuration Migration Tool which is designed to plug this gap. You use a GUI to define a schema - which tables/rows should be exported and imported - and then the tool can export the resulting data to XML files and back into other environments. You can also do this in an automated way.

Sounds perfect!?

Nearly… except the CMT has some big limitations and issues.

CMT limitations

Here are the most important limitations I’ve hit in the past when using CMT:

1. Some tables and columns are blacklisted
   For instance, CMT cannot set the “business unit” column on any table. It cannot set image columns. There are many others.
2. It’s not easy to vary/correct the data that’s imported per environment.
   Despite what I said above about 100% match being needed, often there are tiny bits of config that need to vary per environment - Things like URLs for integrations/links. Some of the D365 config data requires substitutions in the data in this way, for instance, Customer Insights - Journey is full of it.
3. Error handling is bad!
   Since we want a 100% match, if the process fails to update/insert a record, we want it to stop with an error so we know. Looking through 1000s of lines of logs after the fact is not fun and if it doesn’t stop and tell us there’s a risk we won’t even know we need to.
4. A single XML file is bad for source control

All of these gaps can be plugged with either a lot of manual effort (not recommended as manual processes fall apart!) or with some clever scripting to pre/post-process the XML data going in/out of CMT and the environment. This is an option, but it’s quite complex.

Going beyond CMT

My solution to the limitations of CMT is to use my PowerShell module Rnwood.Dataverse.Data.Powershell to take control and do a similar thing:

1. We will create a file that lists the actions to take when exporting data - list of tables/columns and transformation.
   When this runs, each record will be exported to a JSON file from the source env.
2. The JSON files can be stored and versioned, for instance in source control and release assets.
3. We will create a file that lists the actions to take when importing data.
   When this runs, it will consume the JSON files and create/update records in the target env.

Step 1. Exporting your data

Firstly get a connection to the source environment. This is how when you are running interactively:

install-module -scope CurrentUser Rnwood.Dataverse.Data.Powershell
$connection = get-dataverseconnection -url https://org60220130.crm11.dynamics.com/ -interactive

See my earlier post for details of how to do this once you are in a non-interactive scenario like AzDO Pipelines.

Now you can export each table you want to include like this:

Get-DataverseRecord -connection $connection -TableName contact | Set-DataverseRecordsFolder data/contacts

In this example, I’m using contact table, because everyone will have it to try, but this is a less likely

When this is executed, you will see a folder with the name data/contacts created inside the current working directory with a JSON file for each record:

The filename is the primary key of the record.

Inside those files looks like this (I’ve cut it down to make it easy to understand)

{
"Id": "2c8c3463-01af-ef11-b8e8-7c1e52038f2b",
"TableName": "contact",
"contactid": "2c8c3463-01af-ef11-b8e8-7c1e52038f2b",
"emailaddress1": "some@other.com",
"firstname": "Some",
"lastname": "Other"
}

There’s a property per column, and the value for this record is there.

You can add more lines for each table you want to include. Just don’t forget to change the table and folder names.

Step 2. Storing and versioning the data

This step is up to you depending on your desired end to end process. You can take the data folder full of JSON files and store it somewhere for later use when you want to deploy to each environment.

When you are doing this in a AzDO/Github Pipeline, I’d recommend committing it to source control and then outputting the files to your build assets.

Step 3. Importing your data

Now you’ll need to get a connection to the target environment. This is how when you are running interactively:

install-module -scope CurrentUser Rnwood.Dataverse.Data.Powershell
$connection = get-dataverseconnection -url https://org60220130.crm11.dynamics.com/ -interactive

Now you need a line for each table to read the JSON files and create/update a record:

Get-DataverseRecordsFolder data\contacts | Set-DataverseRecord -Connection $connection -Verbose

If you have multiple tables that depend on each other, you will need to put them in dependency order.

When you run this, the -verbose parameter makes it output some info about what happened. It will tell you which records got created, which records got updated, and which records did not need updating:

Taking It Further

So now we’ve got something equivalent to CMT. But that doesn’t solve some of the challenges we mentioned at the start. How can we modify the records as they are being imported?

Get-DataverseRecordsFolder data\contacts | ForEach-Object {
  $_.firstname = "Changed it"
  $_
 | Set-DataverseRecord -Connection $connection -Verbose

This example does a really simple static replacement:

• The ForEach-Object loop receives each record as a variable $_ and we can do anything we like with it. The properties on this variable correspond with what you can see in the JSON file.
• At the end of the loop, we must return it ($_ on its own line) if we want the record to be processed. We could do this conditionally if we only want certain records to be included.

Of course, a real use case could do anything here. For instance, reading the environment-specific values from parameters/environment variables.

Summary

Using your own PowerShell script instead of CMT doesn’t have to be hard. Once you’re you’ve got a helper and you’re over the little bits of learning, you can unlock the ability for it to be far more complete, flexible and reliable.

Give it a go and let everyone know what you think!