Most mid-market UK businesses we meet still have SSIS — sometimes SSIS plus SQL Agent, sometimes SSIS plus a bolt-on of bespoke .NET. It works. It was expensive to build. It costs less than anyone realises to run. But it does not scale, it does not test, and the engineers who wrote it are retiring.
We have migrated three clients from SSIS to Snowflake in the last two years. This is the playbook we now use. It takes twenty-four weeks. We do not start shorter.
Weeks 1–2: the audit
Before anything, the audit. Every SSIS estate is worse than it looks. The typical pattern:
- Two to five hundred packages
- Twenty percent of them have not run in twelve months
- Ten percent of them have never been observed to produce an output — they are scheduled but dormant
- A small number of packages do ninety percent of the work
- One package runs for six hours and nobody understands it
The audit produces a list of what to migrate, what to rewrite, what to retire, and a ranking of which packages to tackle first. Any migration that does not start with this audit is flying blind.
Weeks 3–6: the platform
Stand up Snowflake, dbt, an orchestrator (we use Airflow or Dagster depending on scale), ingestion (Fivetran for SaaS, Airbyte or custom for internal sources), and a CI/CD loop. Governance and cost controls from day one. Three environments. Nothing migrates yet.
This is the phase people try to shorten. Do not. A wobbly platform at week six is a catastrophic platform at week eighteen.
Weeks 7–20: the migration
We migrate in waves, one business domain at a time. Each wave follows the same structure:
Wave structure (per domain, 2–3 weeks)
- Source onboarding. Ingestion for the domain's sources, landing raw in Snowflake.
- Model rebuild. dbt models that recreate the domain's outputs. We explicitly do not rewrite metric definitions during migration — same name, same number.
- Reconciliation. Daily, in production, row-level, against SSIS output.
- Parallel run. Minimum four weeks. Longer for critical finance domains.
- Retirement. SSIS packages and SQL Agent jobs for the domain are turned off with owner sign-off.
The mistake we see in failed migrations: using the migration as an excuse to rewrite everything. The new stack should produce the same numbers the old stack produced. Improve the numbers in a later sprint, after the cutover. Otherwise the business cannot trust either system.
Weeks 20–24: consolidation
By week 20 you are typically 80% retired. The last 20% is harder: orphaned packages, one-off scripts, forgotten integrations. Budget four weeks of deliberate retirement, with executive cover to say "this is not migrating". On our most recent engagement, this phase produced a list of forty-one packages that had been running for three years without an owner.
The honest cost picture
The operating saving is rarely the real case. The real case is that your SSIS estate is a single-point-of-failure sitting on the desks of two or three engineers who will retire in the next five years. The migration is not a cost-optimisation; it is a succession plan.
Things that nearly went wrong
On one manufacturing migration, a single SSIS package was producing a month-end cashflow report that finance had grown to rely on. Nobody could find who owned it. We discovered it in the audit, but we did not migrate it — we re-implemented it in dbt from first principles with the CFO, using the opportunity to fix two known definitional errors in the legacy report. Two weeks of finance time; a better number; a clean retirement. This only worked because we found it in week two, not week twenty-two.
Every SSIS migration has one package nobody understands. Find it early, or it will find you late.
What we will push back on
- "Let's do it in twelve weeks." On any SSIS estate over 100 packages, twenty-four weeks is the shortest honest plan. We have shipped in twelve on smaller estates; we will not quote twelve on a three-hundred-package estate.
- "Rewrite the metrics while you're at it." No. Migrate first. Improve after.
- "We'll keep SSIS running as backup." For thirty days, yes. For twelve months, this is how you end up operating both systems forever.
- "Skip dbt, put SQL in the orchestrator." Possible. Not what we would recommend. dbt gives you tests, lineage, docs and a contract with analytics engineers. Retro-fitting that is more expensive than adopting it.