# Migrate Data Skill

Migrate data between Dataverse environments with full fidelity — GUID preservation, relationship integrity, date preservation, and state transitions.

## When to Use

- Moving data from dev/test to another environment (or prod down to test)
- Refreshing a test environment with production-like data
- Migrating records between environments that share the same solution schema

## Prerequisites

- Both source and target environments must be accessible via the MCP (registered in orchestrator)
- Target environment must have the same solution/schema as source (import the solution first — use `/deploy-solution` skill if needed)
- The migration helper plugin DLL (`CroweAT.MigrationHelper`) must be available (pre-built at `plugins/CroweAT.MigrationHelper/bin/Release/net462/`)

## Phase 0: Planning — Migration Analysis Agent

**MANDATORY: Do NOT skip this phase.** Before migrating any data, you must analyze both environments and present a plan to the user.

### Step 0a: Ask the User

Gather:
- Source environment name
- Target environment name
- Which tables to migrate (specific list, "all custom", or "solution:SolutionName")
- Publisher prefix for carrier columns (e.g., "mcpt", "cr1a2")
- Whether to preserve modifiedon dates (recommended: yes)

### Step 0b: Run Analysis

```
dataverse_analyze_migration(
  source_environment_name: "Source_Env",
  target_environment_name: "Target_Env",
  tables: '["table1", "table2", ...]'  // or "custom" or "solution:SolutionName"
)
```

### Step 0c: Review and Present Plan

Act as a **migration planning agent** — review the analysis output and present to the user:

1. **Migration order** — the dependency tiers (which tables go first)
2. **Record counts** — how many records per table
3. **Schema mismatches** — any columns missing in target (BLOCKING if critical)
4. **User mapping** — unmapped users and proposed fallback strategy
5. **Currency mapping** — auto-mapped by ISO code, flag any gaps
6. **Warnings** — self-referential lookups, activity entities, large tables
7. **Estimated time** — based on record counts (~100 records/second)

**Ask the user to confirm before proceeding.** If schema mismatches exist, resolve them first.

## Guardrails

- **Always run `dataverse_analyze_migration` first.** This identifies record counts, relationships, dependencies, and potential issues before any data moves. Never start migrating without the analysis.
- **GUID preservation is critical for relationships.** If records in the target will be referenced by other records (via lookups), their GUIDs must be preserved. Use the `preserve_guids: true` option.
- **Migrate in dependency order.** Parent tables before child tables. If Contact has a lookup to Account, migrate Accounts first so the lookup GUIDs exist when Contacts are migrated.
- **Date preservation requires carrier columns.** Dataverse auto-updates `modifiedon` on write. To preserve original dates, you need carrier columns (custom date fields that hold the original values) — the migration analysis tool identifies when this is needed.
- **Never migrate to production without testing in a sandbox first.** Run the full migration against a dev/sandbox copy of the target environment before touching production.

## Phase 1: Infrastructure Setup

For each table being migrated (in the TARGET environment):

### Step 1a: Register Migration Plugin

```
dataverse_register_plugin_assembly(
  environment_name: "Target_Env",
  name: "CroweAT.MigrationHelper",
  content: "<base64 DLL>",
  plugin_types: "CroweAT.MigrationHelper.PreUpdateModifiedOnPlugin"
)
```

Only register once — check if it already exists first.

### Step 1b: For EACH Table — Create Carrier Column + Plugin Steps

```
// Create carrier column
dataverse_create_column(
  environment_name: "Target_Env",
  table_name: "account",
  schema_name: "{prefix}_MigrationModifiedOn",
  display_name: "Migration ModifiedOn (temp)",
  column_type: "DateTime"
)

// Register plugin step on Create
dataverse_register_plugin_step(
  environment_name: "Target_Env",
  plugin_type: "CroweAT.MigrationHelper.PreUpdateModifiedOnPlugin",
  message: "Create",
  table_name: "account",
  stage: "PreOperation",
  mode: "Synchronous",
  configuration: "{prefix}_migrationmodifiedon"
)

// Register plugin step on Update
dataverse_register_plugin_step(
  // same as above but message: "Update"
)
```

**This creates an unmanaged layer on managed tables. The cleanup in Phase 4 removes it cleanly.**

## Phase 2: Data Migration

Migrate tables in **dependency tier order**. Tables in the same tier can run in parallel.

### For Each Table:

```
dataverse_migrate_table(
  source_environment_name: "Source_Env",
  target_environment_name: "Target_Env",
  entity_set: "accounts",
  entity_logical_name: "account",
  primary_id: "accountid",
  columns_to_migrate: '["name", "telephone1", ...]',
  lookup_columns: '[{"name":"primarycontactid","target_entity":"contact","type":"standard","nav_property":"primarycontactid","target_entity_set":"contacts"}]',
  carrier_field: "{prefix}_migrationmodifiedon",
  is_activity: "false",  // set true for email, task, phonecall, appointment
  batch_size: "100",
  parallel_batches: "10"
)
```

Poll with `dataverse_check_bulk_operation(operation_id: "...")` until complete.

### Lookup Column Configuration — CRITICAL

For lookup columns, you MUST provide:
- `name`: the attribute logical name (e.g., `mcpt_vendorid`)
- `target_entity`: the target entity logical name (e.g., `mcpt_vendor`)
- `type`: `"standard"`, `"polymorphic"`, or `"currency"`
- `nav_property`: the NAVIGATION PROPERTY name for `@odata.bind` (PascalCase, e.g., `mcpt_VendorId`). Get this from the analyze output.
- `target_entity_set`: the target entity set name (e.g., `mcpt_vendors`)

**For polymorphic lookups** (customerid, regardingobjectid, ownerid): set `type: "polymorphic"`. The migration automatically reads the entity type annotation from each record and constructs the correct `@odata.bind`. You only need to list the lookup once — it handles all target types dynamically.

### Activity Entities (email, task, phonecall, appointment)

Set `is_activity: "true"`. This enables:
1. Party list migration (From/To/CC/BCC) — runs BEFORE state transitions
2. Correct entity set usage (e.g., `emails` not `activitypointers`)

### State Transitions

The orchestrator automatically:
1. Creates all records as Active/Open (default)
2. After all upserts complete, PATCHes state for non-Active records (Completed, Canceled, etc.)

The orchestrator automatically handles special state transitions for standard CRM entities:
- **Opportunity Won**: calls `WinOpportunity` action (not simple PATCH)
- **Opportunity Lost**: calls `LoseOpportunity` action
- **Incident Resolved/Cancelled**: calls `CloseIncident` action (auto-creates incidentresolution record)
- **Lead Disqualified**: simple PATCH (works natively)
- **Lead Qualified**: attempts PATCH with `BypassCustomPluginExecution` to avoid `QualifyLead` side effects (which would create duplicate account/contact/opportunity records). If bypass fails due to privileges, the error is logged and the lead stays in Open state for manual resolution.
- **All other entities**: standard PATCH with statecode/statuscode

### Notes & Attachments (annotations, activitymimeattachments)

Notes (annotations) and email attachments (activitymimeattachments) are child records that must be migrated AFTER their parent records exist in the target. These use the same `migrate_table` tool with special considerations:

**Annotations (notes with file attachments):**
```
dataverse_migrate_table(
  entity_set: "annotations",
  entity_logical_name: "annotation",
  primary_id: "annotationid",
  columns_to_migrate: '["subject","notetext","filename","mimetype","isdocument","documentbody","objecttypecode"]',
  lookup_columns: '[{"name":"objectid","target_entity":"account","type":"polymorphic","nav_property":"objectid_account"}]',
  batch_size: 10,   // SMALL — documentbody can be multi-MB base64
  parallel_batches: 3
)
```

Key points:
- `objectid` is polymorphic — uses `objectid_{entitylogicalname}@odata.bind` pattern (e.g., `objectid_account`, `objectid_contact`, `objectid_incident`)
- The migration automatically reads `@Microsoft.Dynamics.CRM.lookuplogicalname` per record to determine the correct target entity
- `documentbody` contains base64-encoded file content — can be very large (10MB+ per record)
- Use **small batch_size (10-25)** and **small parallel_batches (2-3)** to avoid memory/payload limits
- Migrate annotations AFTER all parent entity records exist in target

**Activity Mime Attachments (email file attachments):**
```
dataverse_migrate_table(
  entity_set: "activitymimeattachments",
  entity_logical_name: "activitymimeattachment",
  primary_id: "activitymimeattachmentid",
  columns_to_migrate: '["filename","mimetype","body"]',
  lookup_columns: '[{"name":"objectid","target_entity":"email","type":"standard","nav_property":"objectid_email","target_entity_set":"emails"}]',
  batch_size: 10,   // SMALL — body is base64 file content
  parallel_batches: 3
)
```

Key points:
- `objectid` on activitymimeattachment points to the parent email (or other activity)
- `body` field contains base64-encoded file content
- Migrate AFTER email/activity records exist in target
- Use small batch sizes for same memory/payload reasons

**Migration order for activity data:**
1. First: activity records (emails, tasks, appointments, etc.)
2. Second: party lists (From/To/CC/BCC) — handled by `is_activity: true`
3. Third: activitymimeattachments (email file attachments)
4. Fourth: annotations (notes on any entity — can be done in parallel with step 3)

### Parallel Execution

Tables in the same dependency tier have no dependencies on each other — migrate them simultaneously:
```
// Tier 0: Start all three at once
op1 = migrate_table(accounts...)
op2 = migrate_table(vendors...)
op3 = migrate_table(categories...)
// Poll all three until complete
// Then proceed to Tier 1
```

## Phase 3: Verification

After all tables are migrated:

1. **Compare record counts**: query each entity set in both environments with `$count`
2. **Spot-check records**: pick 3-5 records per table, compare field values, verify lookups resolve
3. **Verify dates**: check `createdon` and `modifiedon` on sample records match between source and target
4. **Check state transitions**: verify Completed/Canceled records have correct statecode in target

## Phase 4: Cleanup — MANDATORY

Remove ALL migration infrastructure from the target:

### Step 4a: Delete Plugin Steps

```
// Find all Migration plugin steps
dataverse_list_plugin_steps(assembly_filter: "CroweAT.MigrationHelper")

// Delete each step
dataverse_delete_plugin_step(step_name_or_id: "{step_id}")
```

### Step 4b: Delete Plugin Assembly

```
dataverse_delete_plugin_assembly(assembly_name_or_id: "CroweAT.MigrationHelper")
```

### Step 4c: Delete Carrier Columns

For EACH table that was migrated:
```
dataverse_delete_column(table_name: "account", column_name: "{prefix}_migrationmodifiedon")
```

This removes any unmanaged layers created on managed tables.

## Key API Nuances

- **GUID preservation**: `PATCH /{entity_set}({source_guid})` — upsert creates with that GUID if absent
- **`overriddencreatedon`**: set on create to preserve source `createdon` timestamp
- **`modifiedon` cannot be set directly** — platform overwrites it. Must use carrier field + Pre-Operation plugin
- **`@odata.bind` is case-sensitive**: use the navigation property name (PascalCase), NOT the attribute logical name
- **Polymorphic lookups**: build from `{base_name}_{entity_type}` using the `@Microsoft.Dynamics.CRM.lookuplogicalname` annotation per record
- **Activity parties**: can only be set on Draft activities. Must update parties BEFORE state transitions.
- **Duplicate detection**: suppressed automatically via `MSCRM.SuppressDuplicateDetection: true` header
- **FetchXML columns**: use logical names (`mcpt_vendorid`), NOT OData `_value` format

## MCP Tools Used

| Tool | Phase | Purpose |
|------|-------|---------|
| `dataverse_analyze_migration` | 0 | Schema analysis + dependency graph + target validation |
| `dataverse_test_connection` | 0 | Verify environment connectivity |
| `dataverse_register_plugin_assembly` | 1 | Register migration helper plugin |
| `dataverse_register_plugin_step` | 1 | Register Create/Update steps per table |
| `dataverse_create_column` | 1 | Create carrier column per table |
| `dataverse_migrate_table` | 2 | Async migration orchestration per table |
| `dataverse_check_bulk_operation` | 2 | Poll migration progress |
| `dataverse_query_records` | 3 | Verify record counts + spot-check |
| `dataverse_get_record` | 3 | Verify individual record data |
| `dataverse_delete_plugin_step` | 4 | Remove plugin steps |
| `dataverse_delete_plugin_assembly` | 4 | Remove plugin assembly |
| `dataverse_delete_column` | 4 | Remove carrier columns |

## Validation

After completing a data migration:
- Compare record counts between source and target for each migrated table
- Spot-check 3-5 records per table: verify field values, lookup relationships, and option set values match
- Verify date fields (especially `modifiedon` if preserved via carrier columns)
- Check for orphaned records — records that reference lookups to records that weren't migrated
- Report: tables migrated, record counts (source vs target), any failures, GUID preservation status