Post Data Migration: Overcoming Challenges with Custom Solutions

Successfully Migrating 20 Years of Data to Production

Overcoming Challenges with Custom Solutions

Recently, I delivered a project involving the migration of 20 years' worth of data to a production environment. This task wasn’t just about moving records—it required meticulous preparation and validation to ensure the data was accurate and the process seamless. Here's how I tackled the challenge and the tools that came into play.

The ETL Journey: From Development to Production

To ensure smooth data extraction, transformation, and loading (ETL), I initially worked within the Development and User Acceptance Testing (UAT) environments. Writing and refining the ETL process in these controlled settings allowed me to:

• Massage the data for consistency.
• Validate transformations against business rules.
• Confirm data accuracy and readiness for production.

Once the ETL process was validated, I performed a bulk delete of data from both Development and UAT to ensure these environments remained clean and uncluttered.

The Power of the Bulk Delete Tool in XrmToolBox

For this project, the Bulk Delete tool in XrmToolBox was instrumental. This GUI-based plugin is designed to handle large-scale record deletions in Microsoft Dataverse or Dynamics 365 environments efficiently. Some of its key features include:

• Customizable Deletion Criteria: Execute deletions based on specific filters.
• Query and Preview: Preview records before deletion to avoid errors.
• Automation: Schedule and automate deletion tasks to save time.
• Efficiency: Overcome the limitations of manual deletion methods like Advanced Find or built-in Bulk Deletion Jobs.

Challenges and the Need for Innovation

Despite the capabilities of the Bulk Delete tool, I encountered an issue: it stopped after deleting a few hundred records. This was likely due to Dataverse API request limits and throttling for large operations, which interrupted the deletion process.

To work around this, I had to manually re-run the tool multiple times with adjusted queries—an inefficient and time-consuming approach.

Custom Solution: A Tailored Console Application

To streamline the process, I decided to build a custom solution—a .NET console application. Below is the complete C# code I developed for this tool, which automates the deletion process and overcomes Bulk Delete Tool limitations:

C# Code:

using Microsoft.Extensions.Configuration;
using Microsoft.Identity.Client;
using Newtonsoft.Json;
using System;
using System.IO;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Threading.Tasks;

namespace PowerAppsRecordDeleter
{
    /// 

    /// Program class for deleting records in Microsoft Dataverse using their GUIDs
    /// 
    class Program
    {
        // Static IConfiguration field to hold configuration settings from appsettings.json
        static IConfiguration? Configuration;

        /// 

        /// Main entry point of the application. Sets up configuration, obtains an access token, and deletes records.
        /// 
        static async Task Main(string[] args)
        {
            // Load configuration from appsettings.json
            ConfigureAppSettings();

            // Retrieve configuration values
            string clientId = Configuration["AzureAd:ClientId"];
            string clientSecret = Configuration["AzureAd:ClientSecret"];
            string tenantId = Configuration["AzureAd:TenantId"];
            string environmentUrl = Configuration["Dataverse:EnvironmentUrl"];
            string entityName = Configuration["Dataverse:EntityName"];

            // Prompt user for comma-separated GUIDs
            Console.WriteLine("Enter comma-separated GUIDs of records to delete:");
            string inputGuids = Console.ReadLine();
            string[] guids = inputGuids.Split(',');

            // Get an access token using OAuth2 client credentials
            string accessToken = await GetAccessToken(clientId, clientSecret, tenantId, environmentUrl);

            // Iterate over each GUID and delete the corresponding record
            foreach (string guid in guids)
            {
                // Trim whitespace to ensure GUID format validity
                bool isDeleted = await DeleteRecord(guid.Trim(), accessToken, environmentUrl, entityName);

                // Log the result of each deletion attempt
                if (isDeleted)
                {
                    Console.WriteLine($"Record with GUID {guid} deleted successfully.");
                }
                else
                {
                    Console.WriteLine($"Failed to delete record with GUID {guid}.");
                }
            }
        }

        /// 

        /// Configures app settings from appsettings.json.
        /// 
        private static void ConfigureAppSettings()
        {
            // Initialize ConfigurationBuilder and load appsettings.json
            var builder = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory()) // Set base path to current directory
                .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true); // Load config file

            // Build the IConfiguration object
            Configuration = builder.Build();
        }

        /// 

        /// Authenticates with Azure AD and retrieves an OAuth2 access token for Dataverse.
        /// 
        /// Azure AD Application (client) ID
        /// Azure AD Application (client) secret
        /// Azure AD tenant ID
        /// Dataverse environment base URL
        /// Access token string
        private static async Task GetAccessToken(string clientId, string clientSecret, string tenantId, string environmentUrl)
        {
            // Build a ConfidentialClientApplication instance for client credentials authentication
            var app = ConfidentialClientApplicationBuilder.Create(clientId)
                .WithClientSecret(clientSecret)
                .WithAuthority(new Uri($"https://login.microsoftonline.com/{tenantId}"))
                .Build();

            // Set the API scope to Dataverse environment URL
            string[] scopes = new string[] { $"{environmentUrl}/.default" };

            // Acquire the token and return the access token string
            AuthenticationResult result = await app.AcquireTokenForClient(scopes).ExecuteAsync();
            return result.AccessToken;
        }

        /// 

        /// Deletes a record from Dataverse Web API using the given GUID.
        /// 
        /// GUID of the record to delete
        /// OAuth2 access token for API authentication
        /// Dataverse environment base URL
        /// Logical name of the entity
        /// Boolean indicating success or failure of the delete operation
        private static async Task DeleteRecord(string recordId, string accessToken, string environmentUrl, string entityName)
        {
            try
            {
                using (HttpClient client = new HttpClient())
                {
                    // Set base address to the Dataverse environment URL
                    client.BaseAddress = new Uri(environmentUrl);

                    // Add the access token to the authorization header
                    client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", accessToken);
                    client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

                    // Construct the API delete endpoint URL with entity name and record GUID
                    string deleteUrl = $"/api/data/v9.2/{entityName}({recordId})";

                    // Execute the DELETE request
                    HttpResponseMessage response = await client.DeleteAsync(deleteUrl);

                    // Return true if the delete operation was successful, otherwise false
                    return response.IsSuccessStatusCode;
                }
            }
            catch (Exception ex)
            {
                // Log any exception that occurs during the delete process
                Console.WriteLine($"Error deleting record: {ex.Message}");
                return false;
            }
        }
    }
}

        

Configuration File (appsettings.json)

JSON Configuration:

{
  "AzureAd": {
    "ClientId": "Your Azure App Registration ID",
    "ClientSecret": "Your Azure App Registration Secret",
    "TenantId": "Your Azure Tenant ID"
  },
  "Dataverse": {
    "EnvironmentUrl": "Your Dataverse Environment URL",
    "EntityName": "Your Table Name"
  }
}
        

Lessons Learned

Delivering this project reinforced the importance of adaptability and innovation in data management. While existing tools like the Bulk Delete tool are incredibly powerful, they’re not always sufficient for complex or large-scale operations. Building custom solutions can bridge these gaps, ensuring efficiency and accuracy.

By combining off-the-shelf tools with bespoke solutions, I successfully navigated the challenges of this project and ensured a smooth migration to production.