SSIS to Databricks Migration: A Step-by-Step Guide
SQL Server Integration Services has been the workhorse of Microsoft data integration for two decades. Now many organisations face a hard choice: keep paying SQL Server licences they only use for SSIS, or move to a cloud-native platform. Databricks is the most common destination for teams that want Spark, Delta Lake and Python/SQL flexibility. This guide explains why the manual rewrite path is risky, how to map SSIS concepts to Databricks, and a faster, safer way to get there with LogicLift.
1. Why SSIS to Databricks is hard
SSIS and Databricks were built for different execution models. SSIS is workflow-oriented: packages move data through control flows and data flows, often relying on SQL Server Agent, file shares and custom components. Databricks is data-parallel: workloads run as Spark jobs on clusters that scale elastically.
That difference shows up in four practical problems:
- Control flow vs. Spark execution: Orchestration steps in SSIS do not map cleanly to Spark transformations. You need a new workflow layer such as Airflow or Databricks Jobs.
- Package complexity: Script tasks, custom components, logging, configurations and lookup caches contain business logic that is rarely documented.
- Hidden dependencies: SQL Server Agent jobs, FTP drops, shared folders and cached lookups all have to be untangled before migration.
- Data validation: Business rules are buried inside transformations. If you miss one, downstream reports break after cutover.
2. The manual rewrite trap
The conventional approach is to hire consultants who rewrite every SSIS package by hand into PySpark or Spark SQL. The maths is not flattering:
- Typical consultancy effort: ~80 hours per complex package.
- At £150–£250/hour, a 100-package estate costs £1.2M–£2M.
- Timeline: 12–18 months before production cutover.
Even after spending that, teams often end up with notebooks that nobody fully trusts. Human translators make mistakes, requirements drift, and the audit trail is whatever the consultants remember to write down.
3. Mapping SSIS concepts to Databricks
Before you can automate the migration, you need a clear mapping between SSIS constructs and their Databricks equivalents. The table below is the reference we use inside LogicLift:
| SSIS concept | Databricks equivalent |
|---|---|
| Package | Databricks Notebook / Job |
| Control Flow | Workflow DAG (Airflow / Databricks Jobs) |
| Data Flow | DataFrame transformations |
| OLE DB Source | JDBC/ODBC read or Delta read |
| Derived Column | Spark SQL expression |
| Lookup | DataFrame join or broadcast |
| Slowly Changing Dimension | Delta Lake merge |
| Script Task | Python function |
| SSIS Logging | Databricks audit + LogicLift parity tests |
The important point is not just syntax translation. It is preserving semantics. A lookup in SSIS is not just a join in Spark; it is a join with the same null-handling, error-redirect and caching behaviour that the original package relied on.
4. A safer migration playbook
Whether you use LogicLift or do the work manually, a disciplined playbook reduces risk. We recommend five phases:
Ingest & inventory
Export every .dtsx package, configuration file and dependency list. The inventory of sources, targets and transforms becomes your migration backlog.
- Migration backlog defined
- Hidden dependencies surfaced
Translate to PySpark / Spark SQL
Convert lookups to joins, slowly changing dimensions to Delta merges, script tasks to typed Python. Preserve semantics, not syntax.
- Databricks-native code
- No SSIS runtime needed
Generate parity tests
Run source and target side by side on synthetic and real data. Compare row counts, aggregates, edge cases and null behaviour.
- Silent changes caught
- Proof before cutover
Human-in-the-loop review
Flag unresolved nodes, ambiguous business rules and TODOs for the data engineering team. Automation handles the bulk; humans handle judgement calls.
- Judgement where it belongs
- Nothing silently guessed
Deploy & orchestrate
Export Airflow DAGs or Databricks Workflows, run in parallel for one or two cycles, keep the old packages warm until you are confident.
- Repeatable, schedulable runs
- Rollback stays warm
Parallel run, then cutover
Switch only after parity holds across full payroll or reporting cycles — the boring steps are the ones that save the programme.
- Risk contained
- Clean cutover
5. How LogicLift automates this
LogicLift turns the playbook above into a repeatable pipeline. You upload your .dtsx packages, choose between a deterministic compiler or an agentic pipeline, and receive:
- Production-ready PySpark notebooks and Spark SQL.
- A migration spec that documents every translation decision.
- Built-in parity tests on synthetic and real data.
- Audit reports for compliance and regulatory sign-off.
- Optional export to Microsoft Fabric notebooks if Databricks is not your target.
The result is typically a 60–80% reduction in migration time and a 90%+ reduction in manual mapping work compared with a traditional consultancy rewrite.
6. ROI summary
- Time: 6–10 weeks for a 100-package estate instead of 12–18 months.
- Cost: fixed platform fee plus reduced engineering effort, instead of open-ended consultancy hours.
- Risk: deterministic audit trail and parity tests for regulated environments.
Ready to move your SSIS estate to Databricks?
Book a free 30-minute migration assessment. We will review your package inventory and show you the fastest path to Databricks.
Frequently asked questions
Can SSIS packages run in Databricks?
SSIS packages cannot run natively in Databricks. LogicLift translates the business logic inside those packages into PySpark and Spark SQL so the same transformations run on Databricks.
How long does an SSIS to Databricks migration take?
A manual rewrite typically takes 12–18 months for a 100-package estate. LogicLift reduces this to 6–10 weeks by automating the translation and parity testing.
What is the cost of migrating SSIS to Databricks?
Manual consultancy rates range from £150–£250 per hour, leading to six-figure bills. LogicLift charges a fixed platform fee and cuts migration effort by 60–80%.
Can you migrate SSIS to Azure Data Factory instead?
Yes. LogicLift can also target Azure Data Factory, Microsoft Fabric and dbt. Many teams choose Databricks for advanced analytics and Spark workloads.