Migrating Dynamics 365 On-Premise to Cloud: Escaping the SSIS Bottleneck with JSONata

If you migrate Microsoft Dynamics 365 from on-premise to the cloud, conventional wisdom points you toward SQL Server Integration Services (SSIS) coupled with a connector like KingswaySoft. It is the industry standard.

It is also exactly why I recently watched a single bank migration drag on for six agonizing months.

The reality of these migrations hit hard when I spoke with Sudhir, a Product Manager on the client team. He was forced into the migration trenches because key people overseeing their Dynamics rollouts had left the company. They had exactly one person across their India/US team building SSIS packages. Due to a combination of workflow fatigue and the clunky SSIS developer experience, their migrations were stalling out.

To hit a tight weekend cutover for Bank A and rescue a hostile project for Bank B, we had to stop clicking through workflow interfaces.

Here is the exact technical and operational blueprint of how we replaced slow, UI-bound SSIS packages with self-contained, JSONata-powered binaries. I will walk you through the strict boundaries we set, how we solved the three fatal flaws of KingswaySoft, and the hybrid architecture we deployed when standard tools failed.

The Operational Boundaries and Technical Context

Before writing a single line of code, Sudhir and I established strict operational boundaries for the bank A migration. When dealing with core banking data, security is absolute:

1. Zero Access: My team would not have access to any client or bank systems.
2. Self-Contained Execution: We would provide a standalone executable binary that client could run locally on their secure servers.
3. The Scope: The binary would ingest a series of flat CSV files and push them to the Cloud Dynamics 365 Web API.
4. Data Sourcing: The CSV files would be generated entirely by the client team by exporting their on-premise Microsoft SQL Server tables (like contacts, account, person).

Sudhir shared a batch of test CSVs. We built the first version of the binary in a single day. It handled OAuth, parsed the CSVs, validated the schema, and executed the migration. We ran a few tests, recorded a Loom video to prove the data flow, and sent it to Sudhir. Once he was convinced, we moved to the QA environment.

The Bottleneck: The 3 Pitfalls of KingswaySoft

When you use KingswaySoft, engineers manually build packages for each Dynamics table. For a massive CRM, this introduces three fatal flaws that crush iteration speed:

1. Workflow Fatigue: You sit and write packages for each table individually. When you repeat the same manual clicking process hundreds of times, it is only a matter of time before you start making mistakes.
2. Copy-Pasta Errors: Engineers frequently copy and paste mapping nodes to save time. KingswaySoft provides a UI to select fields, but humans still forget to update the target attributes, leading to bad data mappings.
3. Opaque Logic in the UI: Workflow UIs look fantastic in a sales demo. But believe it or not, for anything more complicated than two if-else conditions, you cannot make sense of what is happening. The mind-boggling amount of effort and time required to see what is happening inside a package is insane.

To add insult to injury, SSIS and Azure Data Factory (ADF) have their own stability quirks. Client engineers frequently spent three to four hours debugging why their SSIS packages simply stopped running, only to realize they just had to restart the ADF to make it work again.

The Engineering Solution: How We Solved the 3 Pitfalls

To fix this, we worked directly with a new Business Analyst on their team. The BA talked to the bank to understand what SQL tables to migrate and how to transform them, documenting everything in a "Migration Checklist."

Here is how we bypassed the KingswaySoft bottlenecks entirely:

1. LLM Automation (Solving Fatigue)

We didn't click through UIs, and we didn't write our mapping files from scratch. We built a templatized script that utilized LLMs to read the BA's checklist and auto-generate the mapping structure. This got us 90% of the way there instantly, leaving only 10% for manual tweaking.

This automation was an absolute lifesaver for Bank A's marketing data. They had a massive amount of cdi tables generated by an external email campaign tool. Manually mapping dozens of cdi tables in SSIS would have taken weeks; our script generated the baseline mappings in seconds.

2. Shifting Left (Solving Copy-Pasta)

We shifted the place where mapping mistakes happen right down to the source. That BA checklist became our literal source of truth. If the migration checklist was correct, the generated mapping was correct. We had numerous cases where the name of the table attributes was wrong in the migration checklist itself—we caught and fixed them before code was ever executed.

3. JSONata and YAML (Solving Opaque Logic)

We implemented the entire mapping logic as a single, declarative YAML file powered by JSONata (a lightweight query and transformation language).

It is much easier to find how you are transforming a specific attribute by just using Cmd+F in your text editor. These mappings dictated row-level ETL logic—instructing the binary exactly when to skip a malformed CSV row, when to run a strict insert, and when to upsert based on existing Alternate Keys (which also made the binary idempotent—you could re-run it safely without creating duplicates).

Here is a simple example showing a status mapping:

# YAML/JSONata mapping — simple status transformation
entity: "account"
source_csv: "BankA_Accounts_Export.csv"
mappings:
  - source_field: "status_code"
    target_field: "statecode"
    # JSONata: map legacy on-prem statuses to cloud integers
    transformation: "status_code = 'Active' ? 0 : 1"

And here is a more representative example—the kind of multi-table normalization that made SSIS joins impractical. Bank B had four separate ticket tables that needed to merge into one cloud entity:

# YAML/JSONata mapping — multi-table ticket normalization (Bank B)
entity: "incident"
source_csvs:
  - "BankB_Tickets_Billing.csv"
  - "BankB_Tickets_Technical.csv"
  - "BankB_Tickets_General.csv"
  - "BankB_Tickets_Escalation.csv"
mappings:
  - source_field: "ticket_title"
    target_field: "title"
  - source_field: "ticket_type_code"
    target_field: "casetypecode"
    # JSONata: normalize four legacy type codes into cloud picklist values
    transformation: >-
      $lookup({
        'BILL': 1,
        'TECH': 2,
        'GEN':  3,
        'ESC':  2
      }, ticket_type_code)
  - source_field: "assigned_group"
    target_field: "ownerid@odata.bind"
    # JSONata: resolve the legacy group name to a cloud team GUID
    transformation: >-
      '/teams(' & $lookup($teamMap, assigned_group) & ')'
  - source_field: "priority_flag"
    target_field: "prioritycode"
    transformation: >-
      priority_flag = 'CRITICAL' ? 1 :
      priority_flag = 'HIGH' ? 2 : 3
skip_when: "$not($exists(ticket_title)) or $trim(ticket_title) = ''"
strategy: "upsert"
alternate_key: "legacy_ticket_id"

The $lookup calls, the conditional skip logic, and the OData binding construction are all visible in one file. Compare that to clicking through nested SSIS workflow nodes to figure out which branch handles escalation tickets.

Because of this setup, our iteration speed improved dramatically. Columns would be missed, CSV data might be absent, or mapping names would be wrong. Debugging these issues took us 20 to 30 minutes, and the fixes were ready instantly. Compare that to KingswaySoft, where the debugging-packaging-running ceremony took around four hours.

Dynamics 365 Web API Survival Guide

Running a custom binary means you interact directly with the Web API. We learned several critical optimizations to prevent the API from choking during Bank A's 4 to 5 million record weekend cutover:

• Disabling Plugins: Dynamics plugins configured to run on every insert or update drastically increase API response times. Disable all non-essential plugins before migrating.
• Date Formatting: On-premise SQL datetimes must be strictly parsed and formatted before hitting the Web API.
• OData Foreign Keys: Specifying foreign key references in the Web API requires exact OData binding syntax (primarycontactid@odata.bind: "/contacts(uuid)").
• Status Updates: Understanding exactly how statecodes and statuscodes need to be passed for various entities is critical to prevent API rejection.
• Service Protection Limits and Retry Logic: The Dynamics 365 Web API enforces service protection limits that will throttle you with HTTP 429 responses. Your binary must handle this: respect the Retry-After header, implement exponential backoff, and never assume a request succeeded without checking the response status. We also handled HTTP 503 (service unavailable) with automatic retries. Without this, any migration at scale will stall or silently drop records.

The Bank B: The Hybrid Architecture Tipping Point

Then came Bank B. This migration had been running for six months, the bank was frustrated, and Sudhir explicitly warned me the stakeholders were hostile.

Bank B was using Dynamics CRM as a custom helpdesk (similar to Salesforce Service Cloud). They had a custom setup where tickets were split across four entirely different on-premise SQL tables based on "Ticket Type." They needed all of them merged into a single Cloud table.

The tipping point for Bank B wasn't just the delay. The Client engineer would spend all day working on the custom logic, feel confident the migration was done, and the customer would come back the very next hour saying the migrated data had broken relational links.

To fix this, we deployed a hybrid architecture. The SSIS packages handled half of the migration (the standard, flat entities), while our custom binaries took over the complex custom logic. We bypassed SSIS joins and used JSONata to normalize their four distinct ticket tables into a single payload. We migrated the million records, and not once did our scripts die randomly.

Post-Migration Validation

Migrating records is only half the job. If you cannot prove the data landed correctly, you have not finished.

We ran validation at multiple levels:

1. Record Count Reconciliation: Compare the total record counts per entity between the source CSV exports and the target Dynamics 365 environment. Any mismatch triggers an investigation.
2. Referential Integrity Checks: Verify that all foreign key relationships (e.g., contact → account, incident → contact) resolve correctly in the cloud environment. This is where Bank B's broken relational links were caught—the custom binary logged every unresolved reference.
3. Field-Level Spot Checks: For critical fields (statuses, dates, currency values), sample records and compare source vs. target values directly.
4. Automated Error Reports: Every failed API call was logged with the source record, the target entity, and the exact error response from the Web API. A single command generated a full error report that could be shared with stakeholders immediately.

The binary's upsert strategy (keyed on Alternate Keys) also meant we could safely re-run the migration after fixing issues without creating duplicate records—which is critical during a weekend cutover when you do not have time for a clean-slate retry.

The Overlooked Requirement: Observability

One major pain point with their SSIS packages was the black box. The business side had no clue what the migration scripts were doing. Just to answer a simple question—"How many records errored out?"—required a whole one-hour ceremony for an engineer to pull the information.

We built observability directly into our tooling:

1. Automated Error Reporting: A single command generated an error report automatically.
2. MS Teams Webhooks: We added notifications that pinged the stakeholders with exact progress updates. Observability is usually the most overlooked area of migrations, but it is crucial.

Overcoming "Microsoft Recommended" Skepticism

To be honest, until the actual Bank A production migration was complete, the Client team was on the edge and unsure. Clearly, SSIS is what they thought was the recommended way to do migrations. The initial QA run was an eye-opener for them.

Use this framework to decide your own migration path:

When you treat your data migration as a strict software engineering project rather than a UI workflow task, you stop crossing your fingers and hoping the package finishes. You build a predictable, fault-tolerant pipeline.

References & Technical Documentation

• Microsoft Learn: Dynamics 365 Web API Limits and Service Protection
• JSONata: The JSON Query and Transformation Language
• Microsoft Learn: Associate and disassociate entities using the Web API
• Microsoft Learn: Configuration Migration Tool
• Microsoft Learn: Dataverse SDK Bulk Operations