Migrating Legacy SQL Server and SSIS to Microsoft Fabric Data Factory and Lakehouse

The Legacy Data Warehouse Crisis: Breaking Free from SQL Server and SSIS Debt

Across Australian mid-market and enterprise organizations, critical business reporting remains anchored to aging on-premises SQL Server instances (2012, 2014, 2016) and sprawling portfolios of SQL Server Integration Services (SSIS) packages developed over a decade ago. While these systems once powered corporate business intelligence, they now impose severe operational bottlenecks:

• Exhausted Nightly Batch Windows: As enterprise transaction volumes grow, nightly ETL routines expand from 2 hours into 6-to-8-hour marathons. Batch jobs frequently overrun into business hours, locking production OLTP tables and delaying executive morning dashboard availability.
• Maintenance Fragility & Technical Debt: Legacy SSIS packages are laden with brittle custom C# Script Components, unmanaged linked servers, hardcoded connection strings, and undocumented stored procedures. When an upstream schema changes or a server reboot occurs, packages fail silently, leaving finance teams with corrupt or incomplete data marts.
• High Total Cost of Ownership (TCO): Organizations pay continuous licensing and maintenance for high-spec physical hardware or costly Azure SQL IaaS virtual machines running 24/7—even though compute is only required during batch processing hours.

Migrating legacy ETL workloads to Microsoft Fabric Data Factory and the Medallion Lakehouse architecture eliminates infrastructure overhead, unlocks cloud elasticity, and bridges legacy data directly into modern Power BI Direct Lake reporting without recurring data movement.

Architectural Destination: The Medallion Lakehouse & OneLake

Modern data engineering replaces monolithic relational tables with an open-format Medallion Lakehouse architecture built on Apache Parquet and Delta Lake inside Microsoft Fabric OneLake:

1. Bronze Layer (Raw Landing & Ingestion): High-throughput Fabric Data Factory Copy Activities extract raw transactional tables from on-premises SQL Server or ERPs via the On-Premises Data Gateway. Data is landed into OneLake in its native schema as append-only Parquet files with zero transformation overhead.
2. Silver Layer (Cleaned, Conformed & Enriched): PySpark notebooks or Dataflows Gen2 read raw Bronze tables to perform schema validation, type casting, deduplication, and business rule enrichment. Complex slowly changing dimensions (SCD Type 1 and Type 2) are managed using high-performance Delta MERGE statements.
3. Gold Layer (Curated Dimensional Models): Curated star-schema data marts (fact and dimension tables) optimized for analytics. Because Gold tables exist as Delta Parquet in OneLake, Power BI Direct Lake mode queries billions of rows with sub-second latency directly from memory without importing data or incurring DirectQuery relational penalties.

Component Mapping: Legacy SSIS vs. Microsoft Fabric

The 4-Stage Migration Execution Blueprint

Stage 1: Automated Discovery & Complexity Triage

We execute automated cataloging scripts against the existing SSISDB catalog and SQL Server metadata tables:

• Cataloging all SSIS packages, data source connections, variables, and project parameters.
• Categorizing packages by complexity: Low (straight table-to-table copies), Medium (aggregations and lookups), and High (custom script components, XML tasks, external DLL dependencies).
• Identifying deprecated T-SQL syntax, cross-database queries, and legacy linked servers for modernization.

Stage 2: Hybrid Gateway & Virtual Network Architecture

To safely bridge on-premises databases with the Microsoft Fabric SaaS cloud without punching inbound firewall holes, Ultron deploys a high-availability On-Premises Data Gateway Cluster or Azure Virtual Network (VNet) Data Gateway. Gateway instances run across multiple nodes with automated load balancing and failover, ensuring continuous data throughput.

Stage 3: Incremental Loading & Change Data Capture (CDC)

Full table re-extracts are completely eliminated. For high-volume transactional tables, we implement modern incremental load patterns:

• Watermark Extraction: Utilizing LastModifiedDate or auto-incrementing identity columns to extract only delta records.
• SQL Server Change Data Capture (CDC): Interrogating SQL Server CDC logs to stream inserts, updates, and deletes directly into Fabric Bronze Delta tables.
• Delta Lake Merge: PySpark notebooks apply ACID-compliant upserts into the Silver layer with automated schema evolution.

# Fabric Notebook: Bronze to Silver Delta Lake Merge with Watermark Tracking
from delta.tables import DeltaTable
from pyspark.sql.functions import col, max as spark_max

bronze_df = spark.read.table("bronze_sales_transactions") \
    .filter(col("IngestionTimestamp") >= spark.read.table("metadata_watermarks") \
            .filter(col("TableName") == "sales_transactions") \
            .select("LastProcessedWatermark").collect()[0][0])

silver_table = DeltaTable.forName(spark, "silver_sales_transactions")

# Execute ACID upsert into Silver table
silver_table.alias("target").merge(
    bronze_df.alias("source"),
    "target.TransactionID = source.TransactionID AND target.PartitionYear = source.PartitionYear"
).whenMatchedUpdate(set={
    "Amount": col("source.Amount"),
    "CustomerKey": col("source.CustomerKey"),
    "Status": col("source.Status"),
    "LastModifiedDate": col("source.LastModifiedDate")
}).whenNotMatchedInsert(values={
    "TransactionID": col("source.TransactionID"),
    "PartitionYear": col("source.PartitionYear"),
    "Amount": col("source.Amount"),
    "CustomerKey": col("source.CustomerKey"),
    "Status": col("source.Status"),
    "LastModifiedDate": col("source.LastModifiedDate")
}).execute()

# Update watermark metadata
new_watermark = bronze_df.select(spark_max("LastModifiedDate")).collect()[0][0]
spark.sql(f"""
    UPDATE metadata_watermarks 
    SET LastProcessedWatermark = '{new_watermark}' 
    WHERE TableName = 'sales_transactions'
""")

Stage 4: Automated Dual-Run Parity Testing & Cutover

Prior to decommissioning legacy SQL servers, the environment undergoes rigorous dual-run validation:

1. Both the legacy SSIS job and the new Fabric pipeline execute in parallel over consecutive business cycles.
2. Automated data reconciliation scripts calculate cryptographic row checksums, row counts, and business measure aggregations (such as monthly revenue and inventory valuations).
3. Once variance achieves 100% parity across all test datasets, downstream Power BI reports are repointed to the Fabric semantic model, and legacy SQL agent jobs are safely disabled.

Fabric Capacity Unit (CU) Governance & Smoothing Architecture

A critical architectural difference between legacy SQL Server infrastructure and Microsoft Fabric is how compute resources are scheduled and billed. On-premises SQL Server requires over-provisioning expensive multicore hardware to endure peak 2:00 AM nightly ETL batch windows. In contrast, Microsoft Fabric operates on Capacity Units (CUs) utilizing a 24-hour background smoothing algorithm.

When an intensive Fabric Data Factory pipeline or PySpark transformation spikes CU utilization beyond the provisioned capacity limit (e.g. an F64 SKU), Fabric automatically spreads that interactive and background workload over future hours. This eliminates immediate pipeline throttling while maintaining predictable monthly cloud operational expenditures (OpEx). Furthermore, all data remains strictly resident within Australian cloud regions (Australia East in Sydney and Australia Southeast in Melbourne), ensuring full compliance with Australian privacy laws and APRA CPS 234 security controls.

Australian Enterprise Impact: Benchmarked Performance Gains

Partnering with a national Australian logistics and distribution enterprise, Ultron Developments migrated over 110 legacy SSIS packages to Microsoft Fabric Data Factory and PySpark Lakehouses. The results were immediate:

• Daily batch processing time dropped from 4 hours 45 minutes to just 24 minutes.
• Eliminated an $85,000 annual on-premises SQL Server core licensing and hardware footprint.
• Enabled near-real-time operational freight tracking for 80 regional Australian transport hubs via Power BI Direct Lake mode.

Explore how Ultron Developments' Microsoft Fabric architects and Power BI data engineers modernize legacy SQL estates across Australia.