Migrating Dynamics 365 Sales Data Between Dataverse Environments via the Web API

We needed to move data — not schema, not solutions, actual records — between two Dataverse environments. Solutions handle customisations. The Data Import Wizard handles CSVs. But for a full, automated, repeatable migration of thousands of records across 27 interconnected Sales tables, preserving GUIDs and relationships? We had to build it ourselves.

This post covers the script we wrote, the problems we hit, and the things that aren’t obvious until you’re knee-deep in Dataverse’s Web API.

Why Not Use Existing Tools?

The obvious question. Microsoft offers several data movement options:

Configuration Migration Tool — works, but GUI-driven and awkward to automate. Doesn’t handle prefix remapping.
Data Import Wizard — CSV-based, generates new GUIDs, breaks cross-record references.
Azure Data Factory / Synapse — heavy infrastructure for what should be a targeted migration.
Power Automate flows — rate-limited, no dependency ordering, painful to debug at scale.

We needed: script-it-once, run-it-repeatedly, remap publisher prefixes between environments, preserve record GUIDs so lookups don’t break, handle dependencies automatically, and deal with the many special cases Dataverse throws at you.

So we wrote Migrate-Dataverse-1.ps1.

The Architecture

The script follows a deliberate sequence. Each step exists because skipping it causes failures downstream.

Step 1: Authenticate

Client credentials flow via MSAL.PS. Two tokens — one for source, one for target. The script refreshes tokens silently before each table migration because a full run can outlast the token lifetime.

.\Migrate-Dataverse-1.ps1 `
    -SourceUrl    "https://source.crm.dynamics.com" `
    -TargetUrl    "https://target.crm.dynamics.com" `
    -SourcePrefix "contoso" `
    -TargetPrefix "fabrikam" `
    -ClientId     "your-app-id" `
    -ClientSecret "your-secret" `
    -TenantId     "your-tenant-id"

Step 2: Discover Tables

The script queries EntityDefinitions on the source environment to get every non-intersect table, its logical name, primary key attribute, and entity set name.

Then it filters. A lot.

System excludes remove tables that can’t or shouldn’t be migrated: workflow definitions, plugin assemblies, async operations, bulk delete jobs, import logs, web resources, solutions — anything that belongs in a Solution rather than a data migration.

Some tables are excluded for subtler reasons:

activityparty — can’t be created as standalone records; must be patched onto parent activities
leadaddress, competitoraddress — auto-managed by Dataverse when their parent records are created
transactioncurrency, uomschedule, uom — exist in both environments with different GUIDs, so they’re remapped by name/code instead of migrated

If you pass -TableFilter, only those tables are processed. If you pass -ExcludeTables, those are additionally skipped. If neither is set, everything that passes the system exclude filter is in scope.

Steps 2b–2d: Build Remap Caches

Three types of records can’t use the same GUID between environments:

Owners (systemuser): User GUIDs differ between tenants. The script caches all active users in the target by full name, then remaps _ownerid_value lookups by matching names. If a user doesn’t exist in the target, the owner field is skipped (the record gets the default owner) and a warning is logged.

Currencies: transactioncurrency records have different GUIDs per environment. The script maps source GUID to target GUID by matching ISO currency codes (GBP, USD, EUR).

Units of measure: uomschedule and uom records are also environment-specific. Mapped by name.

This is the part that catches people off guard. Most migration tools assume GUIDs are portable. They are for your custom data. They aren’t for platform-managed reference data.

Step 3: Dependency Graph & Topological Sort

This is where it gets interesting. Dataverse tables have ManyToOne relationships — an opportunity references an account, a quote references an opportunity, a quote detail references a quote AND a product. If you try to create a quote detail before the quote exists, it fails.

The script:

Fetches ManyToOneRelationships for every table in scope
Builds a directed graph: table A depends on table B if A has a lookup to B
Runs Kahn’s algorithm (topological sort) to get a safe migration order
Identifies any tables caught in circular dependencies

The result is written to migration-order.log so you can review it before (or after) running.

Circular Dependencies

They happen. Account has a lookup to Contact (primary contact). Contact has a lookup to Account (parent account). Neither can go first.

The script handles this with a two-pass strategy:

Pass A2 (stubs): Create the records but skip their lookup fields. This gives every record a GUID in the target without needing its references to exist yet.

Pass B (patch): Re-read the source data and PATCH all the lookup fields. Now every referenced record exists, so the binds resolve.

Detail/child tables (opportunityproduct, quotedetail, salesorderdetail, invoicedetail, productpricelevel) are special-cased: they keep their parent lookup even in the stub pass, because their parent records were created earlier in the same pass and they can’t exist without a parent.

Step 4: The Actual Migration

For each table, in dependency order:

Refresh tokens — silent re-acquisition
Fetch writable attributes from the target’s metadata — only send properties the target can actually accept
Fetch all records from the source with odata.include-annotations="*" — this gives us the annotation properties needed to resolve lookups
For each record, build a clean payload:
- Strip the primary key, OData annotations, and read-only properties
- Convert _xxx_value lookup fields to @odata.bind references
- Apply owner/currency/UoM remapping where needed
- Remap publisher prefixes on column names
- Capture statecode/statuscode but don’t include them in the initial upsert
PATCH (upsert) to {entityset}({same-guid}) — this creates or updates, preserving the source GUID
Set state if non-default and not deferred

The PATCH-with-same-GUID approach is key. It means lookups between records just work — the GUID in the source’s lookup field points to the same GUID in the target.

Product Activation

Products in Dynamics 365 have a publish lifecycle. They’re created in Draft state, and you must call PublishProductHierarchy to make them Active. But product price list items (productpricelevel) reference products, and line items reference products — so the products need to be Active before those records can reference them.

The script creates products in Draft (state deferred), then runs a dedicated activation step that:

Ensures each product is in Draft state (statecode = 0)
Calls the PublishProductHierarchy action
Continues to the next table

This happens after products are created but before line items are processed — the dependency sort naturally puts products before their dependents.

Step 5: Activity Parties

This deserved its own step. Activity parties (the To, From, CC, BCC, Required Attendees, etc. on emails, appointments, phone calls) cannot be created as standalone records via the API. You must PATCH them onto their parent activity via the collection navigation property.

The process:

Reset all migrated activities to Open/Draft state (parties can’t be modified on completed activities)
For each activity, fetch its parties from the source
Build a party array with participation type masks and partyid bind references
PATCH the activity with the party array

System user parties get the same name-based remapping as owner fields.

Step 6: Deferred State

The final step walks through every table that had its state deferred (activities, quotes, sales orders, invoices) and sets the correct statecode/statuscode from the source.

Products are skipped here — they were handled by the publish step. Everything else gets a simple PATCH with just the two state fields.

The 27 Sales Tables

For reference, here’s what a typical Dynamics 365 Sales migration covers. The dependency graph handles the ordering, but knowing what’s in play helps with planning:

Reference/config data (no dependencies, migrated first):

pricelevel (price lists)
discount / discounttype
territory
competitor

Core entities:

account
contact
lead

Sales process:

opportunity → opportunityproduct (line items)
quote → quotedetail
salesorder → salesorderdetail
invoice → invoicedetail

Products & pricing:

product
productpricelevel (price list items)

Activities:

email, phonecall, appointment, task, letter, fax
socialactivity, recurringappointmentmaster

Supporting:

customeraddress
annotation (notes & attachments)
connection, connectionrole

What Went Wrong Along the Way

Every migration tool has a war stories section. Here are ours.

Lookup Annotations Aren’t Always Present

The _xxx_value fields give you the GUID, but you need the OData annotations to know which entity type and which navigation property to bind to. Without odata.include-annotations="*" in the request, you get the GUID but no context. A GUID alone is useless for building an @odata.bind reference — the same GUID format could point to an account, a contact, or a systemuser.

Writable Attribute Filtering Matters

Sending a read-only property in a PATCH request doesn’t always fail gracefully. Some return 400 errors. Some silently ignore the property. Some fail with cryptic messages about “property cannot be set”. Fetching IsValidForCreate/IsValidForUpdate from the target metadata and filtering the payload is essential.

State Changes Are Order-Dependent

You can’t set a quote to “Won” if its opportunity is still “Open”. You can’t complete an activity if its required parties don’t exist yet. You can’t deactivate an account that’s the primary contact’s parent if the contact hasn’t been created. The state deferral pattern — create everything in default state, then set states in a final pass — sidesteps most of these issues.

Token Expiry During Large Migrations

Client credential tokens last 60–90 minutes. A migration of thousands of records across 27 tables, with metadata lookups for each table, can easily exceed that. The script refreshes tokens before each table, using MSAL’s silent acquisition (which returns a cached token if it’s still valid, or gets a new one if it’s expired).

Activity Party Participation Types

Each party on an activity has a participationtypemask — 1 for From, 2 for To, 3 for CC, etc. If you don’t include this in the party payload, Dataverse guesses. It guesses wrong. Always include the mask.

Running It

Basic invocation:

# Full migration (all discoverable tables)
.\Migrate-Dataverse-1.ps1 `
    -SourceUrl    "https://source.crm.dynamics.com" `
    -TargetUrl    "https://target.crm.dynamics.com" `
    -SourcePrefix "contoso" `
    -TargetPrefix "fabrikam" `
    -ClientId     "..." `
    -ClientSecret "..." `
    -TenantId     "..."

# Single table (for testing or re-running a failed table)
.\Migrate-Dataverse-1.ps1 ... -TableFilter "opportunity"

# Dry run
.\Migrate-Dataverse-1.ps1 ... -WhatIf

# Exclude specific tables
.\Migrate-Dataverse-1.ps1 ... -ExcludeTables "annotation,socialactivity"

The script produces:

Console output with progress per table
migration-order.log — the resolved dependency order
migration-errors.log — details of any failed upserts

A typical run against our 27-table Sales dataset: 1,112 records upserted, 0 failed. Repeatable — running it again upserts the same records (idempotent by design, since PATCH with the same GUID is an update).

What This Doesn’t Cover

To be clear about the boundaries:

Schema migration — use Solutions for that. This script moves data, not table definitions or forms or views.
Binary content — file columns and image columns store binary data that doesn’t come through in the JSON payload. Those need separate /$value endpoint calls.
Many-to-many relationships — intersect tables are filtered out. Associate/disassociate requests would be needed for N:N relationships.
Audit history — read-only via the API. Export to Data Lake if you need it.
Business Process Flow stage data — BPF definitions migrate via Solutions, but which-record-is-at-which-stage is data. Not yet handled.
Large binary attachments — annotation records with file attachments larger than the target’s configured limit will fail.

Lessons

Preserve GUIDs. PATCH upsert with the source GUID means every lookup just works without a mapping table. This single decision eliminated an entire class of problems.
Let metadata drive the process. Don’t hardcode table lists or column lists. Query EntityDefinitions, Attributes, and ManyToOneRelationships and let the script discover what needs doing.
Defer state changes. Create everything in the default state. Set the real state at the end. This avoids 90% of ordering issues.
Two passes beat one pass for cycles. Stubs first, lookups second. Simple, reliable, no need to detect which specific lookup is causing the cycle.
Token refresh is not optional. Any migration that touches more than a handful of tables will outlast a token. Refresh silently before each table.
Test with -WhatIf and -TableFilter. Migrate one table at a time during development. The dependency graph is nice, but seeing each table succeed individually builds confidence.

The script is in the repository. It’s been tested against production Dynamics 365 Sales environments. The companion post on Customer Insights migration covers extending it for CI-specific tables with a -CustomerInsights switch.