Data Migration – How to migrate exchange rate in multicurrency systems

There are still some customers that use some older, on-premise Dynamics version. Sooner or later, they might decide to migrate their system to the newer version. In such scenarios, apart from migrating schema and functionality, data needs to be migrated.

In most cases, we use SSIS Packages with KingswaySoft SSIS Integration Toolkit for Microsoft Dynamics 365 to migrate data from older MS Dynamics versions. It is a powerful tool that allows developers to build ETL processes in a simple way. It does contain advanced Dataverse connectors with many fancy utilities.

Issue

Recently, when implementing the above-mentioned scenario, we have encountered a requirement to preserve an original exchange rate for existing records, when migrating such data from legacy multicurrency on-premise system. The only problem in such a case is that base currency fields are recalculated, once any currency field is updated, based on the currency exchange rate from Transaction Currency table. Moreover, the out-of-the-box Exchange Rate field is read-only (it can’t be populated manually on record level).

Inefficient Solution

At first, we thought about a solution, which groups data records per exchange rate value in the original system. Then for each of the groups we would update the Exchange Rate value in the Transaction Currency table and all records with exchange rate. Such a scenario would work. However, it is very slow and may cause a headache for developer – it is simple if we must migrate 1 table with not too many records, but for a bigger solution it is an issue.

Solution

After some investigations, we found a more generic, simpler, and faster way. It requires some more effort at the beginning, but later it enables Exchange Rate migration for many tables in a generic way.

What exactly must be done?

Firstly, let’s create new fields. On each of the tables, where we will have to migrate currency related data, we need to add a new decimal field, with the same schema name of type decimal, with highest precision possible. In our case it will be a field called Old Exchange Rate (odx_oldexchangerate), which we have added on Account, Contact and Opportunity. This field will be populated with data from Exchange Rate (schema name exchangerate) field from legacy system.

The next thing we must do is to implement 2 simple Dataverse plugins. The first one will be triggered on create and update of any currency fields, currency lookup field and status reason, on the tables, that we migrate.

public class EnableExchangeRateMigration : IPlugin
{
    public const string OldExchangeRateVariableKey = "MigrateOldExchangeRate";

    public void Execute(IServiceProvider serviceProvider)
    {
        var context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
        var target = (context.InputParameters["Target"] as Entity).As<ICurrencyEntity>();
        ICurrencyEntity preImage = null;

        if (context.PreEntityImages.ContainsKey("PreImage"))
        {
            preImage = (context.PreEntityImages["PreImage"]).As<ICurrencyEntity>();
        }

        decimal? oldExchangeRate = target.odx_oldexchangerate ?? preImage?.odx_oldexchangerate;

        if (oldExchangeRate != null && oldExchangeRate != 1m && oldExchangeRate != 0)
        {
            context.SharedVariables.Add(OldExchangeRateVariableKey, oldExchangeRate);
        }
    }
}

Basically, what the plugin does, once triggered, it takes old exchange rate value from our new field and copies the value to the shared variable.

Moreover, a simple interface ICurrencyEntity is used to make this logic more generic. It contains properties for Exchange Rate, TransactionCurrencyId and odx_oldexchangerate columns in data model. As you can see, we are using early bounds. Sample implementation of ICurrencyEntity interface:

public interface ICurrencyEntity
{
    decimal? odx_oldexchangerate { get; set; }

    decimal? ExchangeRate { get; }

    EntityReference TransactionCurrencyId { get; set; }
}

public partial class Account : ICurrencyEntity
{
}

public partial class Contact : ICurrencyEntity
{
}

public partial class Opportunity : ICurrencyEntity
{
}

Here is how the plugin registration looks like after we register plugins:

We must register it for every entity in 2 steps. First one for create and second one for update of Money attributes, status reason and currency lookup.

For each of the Update steps, we must add PreImage with odx_oldexchangerate attribute:

The second plugin will be registered on RetrieveExchangeRate message. It is a message called by internal logic, whenever exchange rate value needs to be recalculated on the record. Whether the plugin finds shared variable called MigrateOldExchangeRate in parent context, then it will return our original exchange rate via RetrieveExchangeRate message output parameters.

What is Shared Variable and how is it bound to Plugin Execution context can be found under the following link: https://learn.microsoft.com/en-us/power-apps/developer/data-platform/understand-the-data-context#shared-variables

public class OverrideExchangeRate : IPlugin
{
    private static bool CanExecute(IPluginExecutionContext context) => context.ParentContext != null
        && context.ParentContext.SharedVariables.Contains(EnableExchangeRateMigration.OldExchangeRateVariableKey)
        && context.ParentContext.SharedVariables[EnableExchangeRateMigration.OldExchangeRateVariableKey] is decimal;

    public void Execute(IServiceProvider serviceProvider)
    {
        var context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
        var tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));

        if (CanExecute(context))
        {
            var newExchangeRate = (decimal)context.ParentContext.SharedVariables[EnableExchangeRateMigration.OldExchangeRateVariableKey];
            tracingService.Trace($"newExchangeRate: {newExchangeRate}");
            context.OutputParameters[nameof(RetrieveExchangeRateResponse.ExchangeRate)] = newExchangeRate;
        }            
    }
}

In Plugin Registration Tool it looks like this:

Step definition:

The last step is to create a data migration package for each of the entities. Remember to map exchangerate to odx_oldexchangerate, other money type and currency lookup fields.

After we run our package, we can see that data was populated with original values, even though exchange rate for currencies in target system are different.

If we have our trace logs enabled, we can see that plugins were triggered:

Things to keep in mind before running such logic on bigger data sets:

·         We should disable any tracing or logs to avoid too many trace-logs

·         We shouldn’t enable bypassing plugins in Dataverse Destination, else our plugin won’t be triggered (check screenshot below)

·         After migration is done, it is advised to disable/remove steps/plugins if we want the logic to work correctly for new or updated records in target system

That’s it! If you would like to improve this solution, you could add some feature flag stored in Environmental Variable to toggle this functionality on demand.