Migration
SQL Server and SSIS to Microsoft Fabric migration: a practical guide
How to move a SQL Server data warehouse and its SSIS packages to Microsoft Fabric: Warehouse or Lakehouse, T-SQL compatibility, rebuilding SSIS in Fabric Data Factory, Power BI on OneLake and cutover.
Hemant Walchale
· 3 min read
For organizations already on Microsoft 365, Azure and Power BI, Microsoft Fabric is the natural home for an on-premises SQL Server warehouse. It keeps T-SQL, brings Power BI closer to the data through OneLake, and replaces several separate services with one capacity. The work is in checking T-SQL compatibility and rebuilding SSIS.
This is the approach we recommend.
1. Inventory the warehouse and the packages
List databases, tables, views, stored procedures, functions and SQL Server Agent jobs, plus every SSIS package (export the .dtsx files from SSISDB) and every Power BI dataset or report that reads from the warehouse. Execution history shows what still runs; retire the rest.
2. Choose the landing zone
- Fabric Warehouse: T-SQL tables, stored procedures and views. The closest fit for SQL Server teams.
- Fabric Lakehouse: Delta tables managed with Spark notebooks. Better for files, semi-structured data and Python.
- Both store data in OneLake as Delta, so a Lakehouse can feed a Warehouse and Power BI can read either.
3. Check T-SQL compatibility
Run each stored procedure and view through Microsoft’s migration tooling for Fabric Data Warehouse where it applies, then check the remainder against the Warehouse’s documented T-SQL limitations. Common rework: features that are unsupported or behave differently, some data types, and patterns that relied on SQL Server-specific behaviour such as triggers or cross-database queries.
4. Rebuild SSIS
| SSIS | In Fabric |
|---|---|
| Data Flow Task (sources, transforms, destinations) | Copy activity plus Dataflow Gen2, or a stored procedure or notebook for the transformation |
| Execute SQL Task | Stored procedure or script activity |
| Precedence constraints | Pipeline activity dependencies (success, failure, completion) |
| Foreach Loop container | ForEach activity |
| Script Task | Notebook activity |
| Send Mail Task | Pipeline notifications or an Office 365 Outlook activity |
| SQL Server Agent schedule | Pipeline schedule or event trigger |
| Parameters and environments | Pipeline parameters and deployment pipelines per environment |
If the deadline is tight, run packages in the Azure-SSIS integration runtime first and rebuild them domain by domain.
5. Load, sync and reconcile
Load history once, then keep Fabric in sync during the parallel run, using Fabric mirroring where it supports your SQL Server version or a CDC tool otherwise. Compare every target table (row counts, totals, row-level differences) and repoint Power BI datasets to Fabric one domain at a time.
6. Retire the old servers
Disable the matching Agent jobs and SSIS packages on the day each domain cuts over, and decommission SQL Server instances and licences as their workloads empty.
The short version
Inventory the warehouse, packages and reports, choose Warehouse, Lakehouse or both, check T-SQL compatibility early, rebuild SSIS as Fabric pipelines and Dataflows, sync during a parallel run, repoint Power BI by domain and retire SQL Server as each domain moves.
Moving SSIS to Databricks instead? See our SSIS to Databricks guide, or browse all migration guides. For a fixed-price plan, see the Data Platform Assessment.
Not sure where your own platform stands? Take the free 3-minute data platform scorecard. Unfamiliar terms are explained in the data engineering glossary.