Moving SSIS to Microsoft Fabric Wasn't a Translation
Last updated on
5 min read

Moving SSIS to Microsoft Fabric Wasn't a Translation


At the beginning, an SSIS-to-Fabric migration looks like a technology exercise. Inventory the packages, identify the sources, replace the data flows, and wire up the new orchestration. There is enough familiar SQL in both worlds to make the job look manageable.

Then the first reconciliation comes back wrong.

The row count is close, but the values do not quite agree. One lookup behaves differently. A retry changes the result. And the old package, which has been doing its job quietly for years, turns out to know things no design document ever mentioned.

That was the real migration work for me: not translating SSIS components, but finding the behavior we had to preserve, the behavior we wanted to change, and the operational assumptions that had to be made explicit.

The old pipeline is a specification, even when nobody intended it to be

A package diagram tells you the order of tasks. It does not necessarily tell you the full meaning of the data.

An apparently straightforward lookup may have several fallback paths and a very particular order of precedence. A branch that looks redundant in isolation can be the reason a large body of historical records resolves correctly at all.

The same applies to type conversions, date handling, exception paths, and what happens when reference data is missing. An engineer can write cleaner SQL than the original package and still produce a different business result.

I learned to treat the legacy output as a behavioral baseline, not as proof that every old decision was correct. The distinction between a technically correct join and a meaningful business answer deserves its own test. When a Fabric result differed, the first question was which rule produced the difference? Only after that could we decide whether to match or deliberately revise it.

We had to migrate the state machine too

SSIS packages often carry operational state indirectly: a file moves folders, a row lands in a log table, a stored procedure updates a watermark, or a downstream package runs only after an earlier task succeeds. Those small conventions collectively define what “loaded” means.

In Fabric, we wanted that lifecycle visible rather than scattered across activities.

The migration moves both transformations and operational state, with historical replay and parity checks alongside the new pipeline.

The important controls were mundane but essential: source identity, content identity, ingestion status, row lineage, reject reasons, retry eligibility, and the ability to undo an accepted load. Without them, “rerun the notebook” is not a recovery procedure.

This is why I now think about content identity separately from filenames and why backout deserves its own design. Underneath the activity graph is a data-state machine that has to survive retries.

The platform boundary is not just syntax

One early temptation was to think of Fabric as SQL Server with a different front end. It isn’t. A Lakehouse Delta table manipulated through Spark and a Fabric Warehouse table manipulated through T-SQL have different execution and write paths.

You cannot assume an operation that worked as a stored-procedure transaction will translate into a Spark MERGE against a Warehouse table. We had to be deliberate about which engine owned each step: Spark for flexible file processing and enrichment, the Warehouse for curated relational serving and publication. Moving data between those boundaries also creates failure points the original SQL Server transaction never had.

That changed the architecture more than renaming tasks did. We treated transformations as a sequence of responsibilities, but chose persistence points around recovery and consumption rather than saving every temporary result. I wrote separately about keeping logical layers without writing every layer.

Parity testing needed historical context

Comparing one new batch against the old warehouse sounds efficient. It can also be misleading.

Legacy outputs may depend on years of source history and evolving reference data. Until the new path has the necessary context, a failed lookup may reflect incomplete inputs rather than a defective join.

We needed a staged approach:

  1. Reconstruct the relevant historical source population and its file identity.
  2. Reconcile each transformation boundary: accepted rows, rejected rows, normalization, and enrichment.
  3. Compare totals by meaningful source and time slices before jumping into individual records.
  4. Drill down to the first differing row and trace its lookup path through both systems.

The last step was often the most productive. One record that passed an early transformation but failed a later lookup could tell us more about the old rule ordering than a page of aggregate percentages.

Even matching row counts could hide an offsetting problem: duplicate enrichment on one side and excluded source rows on the other. The reject-table blind spot and join-driven duplicate problem were reminders to follow a row from the point where it entered the system.

Parallel processing exposed new publication problems

Processing independent files concurrently was an obvious opportunity in Fabric. We could clean and enrich several slices at once. But independent inputs do not guarantee independent writes to a shared fact table.

Two jobs publishing overlapping business periods can race, retry into duplicates, or overwrite a boundary the other job just committed. We had to distinguish parallel preparation from controlled publication. Sometimes the simplest answer is to let workers produce staged outputs and have one final publisher own the affected fact partitions or keys.

The same caution applies to watermarks. A date-window refresh can be entirely sensible for mutable records; an ingestion watermark can be better for immutable batches. Neither becomes safe merely because the new orchestration runs on a schedule. The retry and late-arrival rules matter more than the choice of variable name. Even a sound incremental design needs to justify its complexity.

Deployment was part of the data contract

There was another kind of mismatch beyond row values: artifact consistency. A schema change can touch the source definition, notebook transformations, Lakehouse staging, Warehouse tables, downstream views, and the semantic model. Missing one link may not fail until the next real execution—or, worse, until someone refreshes a report.

That pushed us toward treating deployments as a graph of dependent artifacts rather than a collection of independently published notebooks. Fabric’s Git and deployment tooling helps, but environment bindings and object dependencies still deserve explicit review.

What I would do first next time

Before rebuilding a large SSIS estate, I would choose one representative flow with ugly data and trace it end to end. Not the cleanest package—the one with fallback lookups, retries, late files, and a painful historical edge case.

From that flow I would document four things: business behavior, source and row identity, commit and recovery boundaries, and parity evidence. Then I would build the Fabric pattern around those. The package inventory and activity mapping would follow.

SSIS had accumulated a lot of hard-won operational knowledge. The migration became much easier to reason about once we stopped treating that knowledge as incidental implementation detail.

Next in “Microsoft Fabric migration and data correctness” We Had Three Layers but Only Needed One Write →