SSIS to Databricks

Architecture

SSIS in. Databricks out.

Deterministic parsers read the estate and emit native Databricks code — not .dtsx packages lifted into a managed SSIS runtime.

SSIS → MigryX parser → PySpark + Delta + Workflows

SSIS

Data flow.dtsx

Control flowTasks + precedence

ExpressionsSSIS syntax

Conn. managers.ispac projects

MigryX Parser

Deterministic parseAI where it helps

Row-level parityBefore cutover

PySpark emitSet-based, not loops

Workflow emitJob DAGs

Databricks

PySpark notebooksSet-based, not loops

Delta LakeACID + time travel

WorkflowsReplaces scheduler

Unity CatalogColumn lineage

Repos + DABsCI/CD promote

MLflowWhere models live

MigryX AI handles the logic parsers cannot resolve alone, and every change it makes goes through the same parity checks. It runs on a model you approve, air-gapped if your estate requires it.

Why Databricks

SSIS wasn't built for lakehouse-scale analytics

Data Flow components → PySpark DataFrame pipelines

OLE DB Source, Derived Column, Lookup, and Conditional Split are single-threaded transforms locked to one server. PySpark distributes the same logic across clusters. Batch jobs that crawled for hours finish in minutes on Databricks.

Script Tasks → Python notebooks

C# and VB.NET Script Tasks are opaque, difficult to test, and locked to the .NET runtime. Python notebooks on Databricks are version-controlled, unit-testable, and run on Spark's distributed engine natively.

Package orchestration → Databricks Workflows

SSIS package chains via Execute Package Task and SQL Agent jobs are fragile, with limited retry and no DAG awareness. Databricks Workflows provide task DAGs, parameterized runs, conditional branching, and built-in failure recovery.

Parser output

SSIS Data Flow to native PySpark

A typical SSIS Data Flow pipeline with OLE DB Source, Derived Column, Lookup, and OLE DB Destination -- converted to native PySpark DataFrames that run on Spark's distributed engine with Delta Lake output.

SSIS Data Flow (.dtsx)

<!-- Package: Customer_ETL.dtsx -->
<!-- Data Flow Task: Load Customers -->

OLE DB Source: [dbo].[RAW_CUSTOMERS]
  SELECT CustomerID, Name, Region,
         TotalSpend, LastOrderDate
  FROM RAW_CUSTOMERS
  WHERE IsActive = 1

Derived Column: [Tier]
  (DT_WSTR,10)(
    TotalSpend > 50000 ? "Platinum" :
    TotalSpend > 10000 ? "Gold" :
    "Standard"
  )

Lookup: [DIM_REGION]
  JOIN ON Region = RegionCode
  OUTPUT: RegionName, SalesManager

OLE DB Destination: [dbo].[DIM_CUSTOMERS]
  INSERT INTO DIM_CUSTOMERS
  (CustomerID, Name, Tier,
   RegionName, SalesManager)

→

MigryX
converts

PySpark on Databricks

# SSIS Data Flow → PySpark on Databricks
from pyspark.sql import functions as F

# OLE DB Source → spark.read.jdbc()
raw = (
    spark.read.jdbc(jdbc_url, "RAW_CUSTOMERS")
    .filter(F.col("IsActive") == 1)
    .select("CustomerID", "Name", "Region",
            "TotalSpend", "LastOrderDate")
)

# Derived Column → .withColumn()
tiered = raw.withColumn("Tier",
    F.when(F.col("TotalSpend") > 50000, "Platinum")
     .when(F.col("TotalSpend") > 10000, "Gold")
     .otherwise("Standard"))

# Lookup → .join()
dim_region = spark.read.table("dim_region")
enriched = tiered.join(
    dim_region,
    tiered.Region == dim_region.RegionCode,
    "left"
).select("CustomerID", "Name", "Tier",
         "RegionName", "SalesManager")

# OLE DB Destination → Delta Lake write
enriched.write.format("delta") \
    .mode("overwrite") \
    .saveAsTable("dim_customers")

OLE DB Source, Derived Column, Lookup, and OLE DB Destination replaced by native PySpark. Data Flow components become DataFrame operations. Output writes to Delta Lake with ACID guarantees.

Coverage