Data Migration to Dynamics 365: What Usually Goes Wrong and How to Fix It

Migrating data into Dynamics 365 can get tricky fast. You’re not just moving rows from one table to another. You’re dealing with entity relationships, system rules, and tools that behave differently depending on volume and structure.

Here’s what often goes wrong, and how to handle it like a pro.

Entity Mapping Isn’t Always One-to-One

You might think you can map Contact to Contact and Account to Account directly. But once you dig in, things get complex.

Let’s say your source system has contacts linked to multiple account types. In Dynamics, contacts can only have one parent account out of the box. You’ll need to split or redesign the structure.

Also, custom fields often need new option sets or transformations. A "Customer Status" field with values like "Hot," "Cold," or "Warm" might need to be converted to global option set values.

Lookup Fields Can Break Your Import

Dynamics 365 uses GUIDs for lookup fields. You can’t import a contact and just write "ABC Corp" in the parent account field. You need the correct GUID or a way to match it to an existing account.

Best way to do this? Create a reference map ahead of time. Export the Account entity with names and GUIDs. Use Power Query or KingswaySoft to create a lookup table so your import tool knows what goes where.

Choosing the Right Tool Matters

For small loads, you can use the built-in Data Import Wizard or Power Query. But once you hit larger datasets or need automation, you’ll need stronger tools.

KingswaySoft works well for SSIS pipelines. It’s good for scheduled loads, transformation steps, and detailed error logs. Power Platform Dataflows are easier to set up but can struggle with lookups, throttling, and large volumes.

If your source is cloud-based and structured, Azure Data Factory can be useful, but you’ll need more dev experience to set it up properly.

CSV Files Are Not Enough for Complex Relationships

CSV imports work when your data is flat. But if you have nested data, like a list of Contacts each with Activities and related Products, it gets messy.

Instead of trying to cram it all into one file, break it down by entity. Load Accounts first, then Contacts, then related records using their GUIDs. This sequence matters because of lookup dependencies.

Always test with a small batch. See what breaks and adjust the order and format.

Real-World Gotchas

Imported records sometimes fail because of required fields that were added later by other customizations.

Date formats often fail silently. "12/31/2024" in one system may be interpreted as "31/12/2024" or not recognized at all.

You may need to create staging tables if your source system has different naming rules or character limits.

Final Tip

Always log everything. Keep track of failed rows, skipped records, and duplicates. Run your loads in a sandbox first and confirm lookup relationships before going live.

At 3ix Consulting, we help teams navigate these steps with proper tooling, testing, and support so nothing gets lost in the move.

Planning your Dynamics 365 migration? Set it up smart from the start.