SSIS to Microsoft Fabric: Migrating a Banking Data Warehouse, Step by Step
Banks are disproportionately Microsoft-centred estates: SQL Server, SQL Server Agent, file shares, Entra ID — and a Power BI footprint that re-imports the warehouse outputs every morning for risk, finance and board reporting. For those estates, Fabric is often the most natural landing zone for an SSIS exit: OneLake instead of file shares, notebooks instead of packages, and a semantic model your reports already speak to. This guide covers how SSIS concepts map to Fabric, where the hidden risk sits in a regulated reporting chain, and a plan that proves parity before cutover.
1. Why Fabric is the natural target for Microsoft-centred bank estates
Most bank SSIS estates grew up around SQL Server and a reporting stack that ends in Power BI every morning: risk extracts, finance reconciliations, customer and account reporting, all landing in shared databases that Power BI re-imports on schedule. Fabric collapses that chain. The lakehouse replaces staging databases, pipelines replace Agent jobs, and the semantic model your analysts already use can read the gold layer directly through Direct Lake — no more morning refresh windows, no more import duplicates of the same data.
That does not make the migration free. The business logic in your packages still has to survive the move — and in a bank, it has to survive with evidence, because the outputs feed reconciliations and reports that Compliance and Finance sign off.
2. Where the risk sits in an SSIS package
- Data flow transformations: derived columns, lookups and aggregations carry the business rules.
- SCD wizard output: effective dating and inferred member handling generated years ago, understood by nobody.
- Script tasks: arbitrary .NET code — the least documented logic in the estate.
- Error outputs and row redirects: rows quietly routed away from the main flow must land somewhere visible in Fabric too — silent row loss is a reconciliation incident in a regulated reporting chain.
- Package configurations: environment differences inherited from .dtsConfig files.
3. Mapping SSIS concepts to Fabric
| SSIS concept | Microsoft Fabric equivalent |
|---|---|
| Package | Notebook + Pipeline activity |
| Control flow | Fabric pipeline orchestration |
| Data flow | Spark job in a notebook or Dataflows Gen2 |
| OLE DB Source | Lakehouse table read / mirrored database |
| Derived Column | Spark SQL expression in notebook |
| Lookup | DataFrame join or SQL MERGE |
| Slowly Changing Dimension | Delta MERGE in lakehouse |
| Script task | Python in a notebook cell |
| Package configurations | Pipeline parameters + environment variables |
| SSIS Logging | Pipeline run history + parity tests |
4. A step-by-step migration plan
Export & inventory
Collect every .dtsx package, .dtsConfig file and Agent job that invokes them. Record sources, targets, transformations and error-output handling per package — and which reports or reconciliations consume each output.
- Migration backlog defined
- Hidden dependencies surfaced
Translate to notebooks
Re-implement data flows as PySpark in notebooks. Convert lookups to joins, SCDs to Delta MERGE, script tasks to typed Python. Preserve semantics, not syntax.
- Fabric-native logic
- Error outputs made explicit
Generate parity tests
Run package and notebook side by side on frozen inputs — including month-end and period-end files — and diff every output, redirects included. Keep the diffs as standing evidence.
- Silent drift caught
- Audit-ready cutover evidence
Human review of exceptions
Script tasks with .NET logic and ambiguous SCD behaviour go to an engineer with the original package attached. Humans judge; they do not transcribe.
- Judgement where it belongs
Wire up pipelines & environments
Rebuild control flows as Fabric pipelines with parameters per environment — making dev/test/prod differences explicit and version-controlled at last.
- Orchestration replaced
- Config drift eliminated
Parallel run & cut over
Run old and new for one full reporting cycle, reconcile the Power BI numbers against the legacy reports — risk and finance sign-off included — then retire packages one subject area at a time.
- Reports match on day one
- Rollback stays warm
5. The pitfalls that actually bite
- Dataflows Gen2 for everything. It is fine for light transforms; heavy or complex logic belongs in Spark notebooks. Choosing per workload saves pain later.
- Security recreated only in the semantic model. In a regulated estate, row-level and column-level security belongs at the data layer — warehouse roles and lakehouse permissions — with the semantic model adding report-level polish. Security that lives only in a report breaks the moment someone queries the lakehouse directly.
- SCD wizard behaviour recreated from memory. The generated .NET code in old packages is the specification — read it.
- Environment differences hiding in .dtsConfig. Surface them during migration, or they reappear as production-only defects.
- Row redirects with no destination. A package that quietly wrote 400 rejected rows to a file must do the equivalent in Fabric, or reconciliations drift with no error raised — and in a bank, reconciliation drift is an incident.
6. Where LogicLift fits
Steps 2–3 are the mechanical heart of the migration. LogicLift reads the .dtsx packages, regenerates the logic as PySpark notebooks and SQL for Fabric lakehouses, and generates the parity suite alongside — so every migrated package ships with proof it reproduces the legacy output. The orchestration, security design and cutover decisions stay with your team, where they belong.
Moving a banking SSIS estate to Fabric?
Book a free 30-minute migration assessment. We will review your package inventory — including the packages feeding risk and finance reporting — and map the fastest safe path to Microsoft Fabric.
Frequently asked questions
Can SSIS packages run inside Microsoft Fabric?
No. Fabric pipelines and notebooks are a different execution model. Migrating means re-implementing the package logic — but for Microsoft estates it is often the most natural target, because Fabric connects directly to Power BI, OneLake and the Microsoft identity stack you already run.
Fabric or Databricks — which should we choose?
If your estate is Microsoft-centred (Power BI, Office 365, Entra ID) and your workloads are BI-facing, Fabric is usually the lower-friction target. If you need advanced Spark, streaming or machine-learning workloads, Databricks has the deeper engine. The migration approach — extract logic, regenerate, prove parity — is identical either way.
How do we reproduce row-level and column-level security from our banking reports?
Enforce security at the data layer, not just in the semantic model. Fabric lakehouses and warehouse roles plus object-level security in the semantic model together reproduce what report-level security did in SSIS-era estates — and enforcing at the layer where the data lives is what a regulated environment expects, because it holds regardless of which tool reads the model.
What replaces SSIS slowly changing dimensions in Fabric?
A Delta MERGE in a notebook implements SCD Type 1 and Type 2 cleanly, with full transaction support and time travel for audit. The SCD wizard behaviour — effective dating, inferred members — has to be reproduced explicitly, which is exactly the kind of hidden logic parity testing exists for.
How do we handle dev/test/prod environments without SSIS configurations?
Fabric environments and pipeline parameters replace package configurations. The migration is a good moment to make environment differences explicit and version-controlled, instead of inherited from .dtsConfig files nobody has read since 2016.