Migrations/SSIS → dbt + Airflow
SSIS to dbt + Airflow Migration
SSIS isn't a warehouse — it's the orchestration layer, and it's where the business logic actually hides. Migrating off it means turning opaque, point-and-click packages into version-controlled, tested transformations your whole team can read, review, and change without fear. We've untangled exactly these estates at Ancestry and across migrations.
Why teams migrate
The triggers we hear most
- 01SSIS packages are black boxes only one person can safely change
- 02There's no version control, code review, or testing on your most critical data logic
- 03Everything is bound to Windows and SQL Server Agent you're trying to leave
- 04Batch windows keep growing and failures are silent until a report is wrong
- 05You're moving the warehouse to the cloud and SSIS doesn't come with it
What makes it tricky
Where SSIS-to-dbt + Airflow migrations go wrong.
The logic is buried in the boxes
Derived columns, conditional splits, and script tasks encode rules that exist nowhere else. We reverse-engineer each package into explicit, documented transformations before we rebuild it — so nothing silently changes.
Orchestration vs. transformation
SSIS conflates moving data and transforming it. We separate the concerns: Airflow (or your scheduler of choice) owns orchestration and dependencies; dbt owns transformations with tests and lineage. That split is most of the durability win.
Lineage and parity
Stakeholders need proof the new pipeline produces the same numbers. We build column-level lineage and run old-vs-new reconciliation per model until parity holds.
How we run it
Parallel-run, reconciliation-gated, reversible.
The same migration discipline our founders ran inside enterprise data teams — the evidence decides each cutover, not the calendar.
- 1
Inventory every SSIS package, its schedule, and its downstream consumers — and flag the ones with no consumer for deletion.
- 2
Reverse-engineer each package's logic into documented, testable dbt models.
- 3
Rebuild orchestration in Airflow with explicit dependencies, retries, and alerting designed in.
- 4
Run old and new side by side, reconciling each model's output until it matches.
- 5
Decommission packages subject-area by subject-area as parity is proven and signed off.
Transformations in version-controlled dbt with tests and lineage, orchestration in Airflow with real observability, and SSIS retired — ETL your team can change confidently instead of fear.
FAQ
Common questions
- How do you migrate SSIS logic without breaking reports?
- We reverse-engineer each package into documented dbt models, then run the old SSIS pipeline and the new one in parallel, reconciling outputs per model until they match — so reports keep producing the same numbers through the switch.
- Why dbt and Airflow specifically?
- They separate the two jobs SSIS conflates: Airflow owns orchestration (dependencies, retries, alerting) and dbt owns transformation (with tests, version control, and lineage). The result is ETL your whole team can read and change — not a black box. We'll adapt the targets to your stack where it makes sense.
- Can we do this without moving the warehouse at the same time?
- Yes. Re-homing SSIS to dbt/Airflow is valuable on its own and is often the first phase of a larger move — it makes the eventual warehouse migration dramatically safer because the logic is finally in code.
→ Start a project
Planning a SSIS to dbt + Airflow migration?
Describe your current estate in three sentences. We'll come back with how we'd phase it, what it likely costs, and whether we're the right team — usually within two business days.