Microsoft Fabric vs SSIS: A Practical Migration Blueprint for BI Teams

1. Fabric vs Traditional SSIS/SQL ETL: What Actually Changes

You already know the marketing slides. Let’s focus on what changes in day-to-day work when moving from SSIS/SQL Agent to Microsoft Fabric.

1.1 Core architectural shift

Traditional stack:

• On-prem SQL Server
• SSIS packages deployed to SSIS Catalog
• SQL Server Agent for scheduling
• File shares / network paths as sources and destinations

Fabric stack:

• OneLake as the central storage layer
• Dataflows Gen2 and Data Factory pipelines for orchestration
• Notebooks (Spark) and Data Engineering for heavy transformations
• Fabric job scheduler (within Workspaces) instead of SQL Agent

1.2 Key differences that drive migration choices

• Storage model
  • SSIS: writes to SQL tables or flat files.
  • Fabric: writes to Lakehouse (Delta) or Warehouse tables.
• Compute engine
  • SSIS: row-by-row data flow engine, tightly coupled to SQL Server.
  • Fabric: Spark for big data, engine-agnostic; Warehouse for SQL-first workloads.
• Orchestration
  • SSIS: control flow inside packages plus SQL Agent.
  • Fabric: Data Factory pipelines with activities, triggers, and integration with other Fabric items.
• Deployment & environments
  • SSIS: SSIS Catalog, environments, configurations.
  • Fabric: Workspaces, deployment pipelines, and environment variables in pipeline activities.

Keep these differences in mind; they drive many of the decisions in the migration blueprint below.

2. Step 1 – Inventory and Classify Your Existing ETL

Before you touch Fabric, you need to know what you’re migrating. Otherwise you’ll miss dependencies and break downstream reports.

2.1 Build an ETL inventory

Capture at least the following for each existing process:

• Package/job name
• Owner / business contact
• Schedule (frequency, runtime window)
• Source systems (files, DBs, APIs)
• Target systems (data marts, cubes, reports)
• Volume (rows per day, rough size)
• Dependencies (other jobs, manual steps)

If you’re using SSIS, generate a list from:

• SSIS Catalog (SSISDB) tables
• SQL Server Agent jobs referencing SSIS packages

Example query to list SSIS packages from SSISDB:

SELECT
    f.folder_name,
    p.project_name,
    pkg.package_name,
    pkg.description
FROM catalog.packages pkg
JOIN catalog.projects p
    ON pkg.project_id = p.project_id
JOIN catalog.folders f
    ON p.folder_id = f.folder_id
ORDER BY
    f.folder_name,
    p.project_name,
    pkg.package_name;

2.2 Classify ETL patterns

Group each ETL process into patterns. Common ones:

• Simple file-to-table loads
  • CSV/Excel → staging table → data mart
• Incremental database loads
  • Source SQL → delta detection → fact/dim load
• Complex transformations
  • Heavy joins, business rules, lookups, SCDs
• Orchestration-only
  • Job that just calls other packages/scripts
• Metadata-driven frameworks
  • One master package that drives many loads

This classification will drive which Fabric tool you choose (Dataflow, Pipeline, Notebook, Warehouse) for each process.

3. Step 2 – Map SSIS/SQL Patterns to Fabric Components

Now translate old patterns into Fabric-native equivalents.

3.1 Typical pattern mappings

1. File-to-table loads

   • From: SSIS Flat File Source + OLE DB Destination
   • To:
     • Dataflow Gen2 (Power Query) for extraction and light transforms
     • Lakehouse table or Warehouse table as destination

2. Incremental SQL loads

   • From: SSIS with change tracking or custom delta logic in SQL
   • To:
     • Dataflow Gen2 or Notebook for delta detection
     • Delta tables in Lakehouse using merge logic

   Example incremental load in a Fabric Warehouse:

   MERGE INTO dbo.FactSales AS tgt
   USING dbo.StgSales AS src
       ON tgt.SalesKey = src.SalesKey
   WHEN MATCHED THEN
       UPDATE SET
           tgt.Amount = src.Amount,
           tgt.ModifiedDate = src.ModifiedDate
   WHEN NOT MATCHED BY TARGET THEN
       INSERT (SalesKey, CustomerKey, Amount, CreatedDate)
       VALUES (src.SalesKey, src.CustomerKey, src.Amount, src.CreatedDate);
   

3. Complex transformations / heavy joins

   • From: SSIS data flows with multiple lookups and script components
   • To:
     • Fabric Notebook (Spark) for heavy lifting
     • Warehouse/Lakehouse SQL for post-processing

   Example Spark transformation in a Fabric Notebook:

   from pyspark.sql.functions import col, current_timestamp
   
   sales = spark.read.table("lakehouse_raw.Sales")
   customers = spark.read.table("lakehouse_raw.Customers")
   
   fact_sales = (
       sales.alias("s")
       .join(customers.alias("c"), col("s.CustomerId") == col("c.CustomerId"), "left")
       .select(
           col("s.SalesId").alias("SalesKey"),
           col("c.CustomerKey"),
           col("s.Amount"),
           current_timestamp().alias("LoadDate")
       )
   )
   
   fact_sales.write.mode("overwrite").saveAsTable("lakehouse_curated.FactSales")
   

4. Orchestration-only jobs

   • From: SQL Agent jobs calling SSIS packages or stored procedures
   • To:
     • Fabric Data Factory pipelines triggering Dataflows, Notebooks, and Warehouse stored procedures

5. Metadata-driven frameworks

   • From: SSIS parent-child packages with configuration tables
   • To:
     • Notebooks or pipelines that read metadata tables from OneLake and loop through entities

3.2 Decide on Lakehouse vs Warehouse

A practical rule-of-thumb:

• Lakehouse-first when:
  • You have large volumes and need schema evolution
  • You want one storage layer for multiple engines (Spark + Power BI)
• Warehouse-first when:
  • Your team is SQL-heavy and Spark-light
  • You want a more “database-like” experience with stored procedures and views

In many migrations, you’ll end up with:

• Raw + curated layers in Lakehouse
• A serving layer in Warehouse for BI models

4. Step 3 – Design Your New Fabric Data Architecture

Before rewriting anything, sketch the target architecture.

4.1 Define layers (bronze/silver/gold)

Use a layered approach, even if you never call it “medallion”:

• Raw (Bronze)
  • Ingested as-is from source systems
  • Minimal transformations, mainly data type alignment
• Clean/Model (Silver)
  • Business rules, joins, standardisation
  • Conformed dimensions and facts
• Serving (Gold)
  • Optimised for reporting
  • Flattened structures, aggregates, and semantic models

Each layer can be:

• Separate schemas in the same Lakehouse
• Separate Lakehouses (if you want stricter separation)
• Warehouse tables for serving

4.2 Plan workspace and environment structure

Typical Fabric workspace strategy:

• Dev workspace
  • All new pipelines, Dataflows, Notebooks created here
• Test/UAT workspace
  • Deployed via deployment pipelines
• Prod workspace
  • Locked down, only automated deployments

Use deployment pipelines to move Lakehouses, Warehouses, and pipelines through environments instead of manual export/import.

5. Step 4 – Build a Pilot Migration (Not a Big Bang)

Don’t start with your biggest, ugliest SSIS project. Pick a small but representative process.

5.1 Choose a pilot candidate

Look for:

• Moderate complexity (some transforms, not just straight copy)
• Clear business owner and test cases
• Limited downstream dependencies

5.2 Implement the pilot in Fabric

1. Create Lakehouse/Warehouse

   • Define tables for raw and curated layers.

2. Build ingestion

   • Use Dataflow Gen2 or Pipeline copy activity to land raw data in Lakehouse.

3. Transform

   • Use Notebook or Dataflow for transformation logic.
   • Optionally use Warehouse stored procedures for final shaping.

4. Orchestrate

   • Build a pipeline with activities in the right order.
   • Add failure handling and notifications.

5. Schedule

   • Configure a time-based trigger matching the old job schedule.

6. Validate

   • Compare Fabric outputs to the old SQL tables.
   • Use row counts and key metrics for sanity checks.

Example Fabric Warehouse stored procedure for a simple dimension load:

CREATE PROCEDURE dbo.LoadDimCustomer
AS
BEGIN
    SET NOCOUNT ON;

    MERGE INTO dbo.DimCustomer AS tgt
    USING dbo.StgCustomer AS src
        ON tgt.CustomerKey = src.CustomerKey
    WHEN MATCHED THEN
        UPDATE SET
            tgt.CustomerName = src.CustomerName,
            tgt.CustomerType = src.CustomerType,
            tgt.ModifiedDate = src.ModifiedDate
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (CustomerKey, CustomerName, CustomerType, CreatedDate)
        VALUES (src.CustomerKey, src.CustomerName, src.CustomerType, src.CreatedDate);
END;

Then call this procedure from a Fabric pipeline activity.

6. Step 5 – Plan Coexistence and Cutover

You will run SSIS and Fabric side by side for a while. Plan for that.

6.1 Coexistence strategy

• Read-only duplication
  • Keep SSIS as the “official” pipeline while Fabric is tested.
  • Run Fabric loads in parallel and compare outputs.
• Report-level switch
  • Point Power BI reports to Fabric Warehouse/Lakehouse once validated.
  • Keep the old SQL data mart as backup for a defined period.

6.2 Cutover checklist

Before you decommission an SSIS package:

• Fabric pipeline successfully runs for a defined burn-in period
• Row counts and key KPIs match within acceptable tolerance
• Power BI datasets updated to use Fabric sources
• Monitoring and alerting in place in Fabric
• Runbook documented for failures and reruns

Only then disable the SQL Agent job or SSIS package.

7. Step 6 – Governance, Monitoring, and Cost Control

Fabric gives you more flexibility, but that also means more to govern.

7.1 Monitoring

• Use pipeline run history for:
  • Success/failure trends
  • Duration changes
• Log key metrics to a monitoring table in Warehouse or a Lakehouse.
• Consider a small Power BI monitoring dashboard:
  • Pipeline status
  • Row counts per layer
  • Data freshness

7.2 Access and security

• Define workspace-level roles (Admin, Member, Contributor, Viewer).
• Use item-level permissions for sensitive Lakehouses/Warehouses.
• Align with existing SQL security where possible (same AD groups).

7.3 Cost awareness

Even without exact numbers, you can:

• Schedule heavy jobs during off-peak hours.
• Avoid unnecessary refreshes (no “every 5 minutes” unless required).
• Consolidate transformations instead of chaining many small notebooks.

8. Common Migration Pitfalls and How to Avoid Them

8.1 Rewriting SSIS line-by-line

Problem:

• Trying to replicate each SSIS component 1:1 in Fabric.

Fix:

• Take the chance to simplify. Many SSIS packages grew organically and can be replaced by cleaner, fewer steps in Fabric.

8.2 Ignoring OneLake

Problem:

• Treating Fabric like “SQL in the cloud” and ignoring Lakehouse.

Fix:

• Use OneLake as the central storage. Even if you’re SQL-first, store curated data in Lakehouse/Delta for flexibility.

8.3 Skipping testing and reconciliation

Problem:

• Assuming “if it runs, it’s fine”.

Fix:

• Build simple reconciliation queries comparing old vs new outputs.

Example row count comparison (run in old SQL and Fabric Warehouse respectively):

SELECT COUNT(*) AS RowCount FROM dbo.FactSales;

Store these counts and track them across runs.

9. A Concrete Next Step

Don’t start by “migrating everything to Fabric”. Instead:

1. Pick one medium-complexity SSIS package.
2. Classify its pattern and map it to Fabric components.
3. Rebuild it using Lakehouse + Dataflow/Notebook + Pipeline.
4. Run both old and new in parallel for a few weeks.

Once that pilot is stable and monitored, you’ll have a reusable pattern for the rest of your SSIS estate. Repeat the pattern, not the panic.