Course 5 · Module 4 — Migrating from SSDT/DACPAC

This module is different. If you’re on SSDT — a .sqlproj, object-per-file CREATE TABLE scripts, a .dacpac you publish with SqlPackage — you’re already declarative. You’re not porting migration scripts; you crossed that bridge years ago. So this isn’t a “stop scripting” pitch. It’s about two things SSDT can’t give you: a build-free workflow, and one toolset that still works the day your shop adds a second engine.

SQL Server only. DACPAC is a SQL Server technology — there’s no DACPAC for PostgreSQL or MySQL. So this module covers SQL Server alone. That single-engine reality is exactly the limitation the back half of this lesson is about.

SSDT, concept for concept

SSDT / DACPAC	SchemaSmith
`.sqlproj` with object-per-file `CREATE TABLE`	Template folder of declarative table files — same idea, no MSBuild project around it
Build → `.dacpac` artifact	No build step — the files are the package
Publish profile (`*.publish.xml`)	`Product.json` + quench config — target, tokens, and deploy switches
`DropObjectsNotInSource`	`DropTablesRemovedFromProduct` (default `true`) — same intent, set in the quench config
`BlockOnPossibleDataLoss`	No automatic abort gate — protect data by setting `DropTablesRemovedFromProduct: false` (never drop) or installing the recyclebin hooks (drop-but-recoverable), and preview destructive changes with WhatIf first
Pre/post-deploy scripts	`Before Scripts` / `After Scripts` folders in the template
SqlPackage / Visual Studio / Azure Data Studio	One CLI — schematongs to extract, schemaquench to deploy

Most rows map almost one-to-one — you’re moving declarative-to-declarative, so the concepts survive intact. The two that change your day-to-day are the build step (gone — there’s no .dacpac to produce; the files you commit are what deploys) and the tooling (one CLI instead of the SqlPackage/Visual Studio stack).

Extract, even though you’re already declarative

Why extract at all when you already have .sql files? Because the cleanest way to land on a SchemaSmith package is to cast it from the live database — the same database your DACPAC already published. That way the package reflects exactly what’s deployed, and you start from a guaranteed-faithful baseline instead of hand-translating your .sqlproj.

cd sqlserver
schematongs --ConfigFile:SchemaTongs.settings.json

=== Casting Summary ===
  Tables:     4 extracted, 0 errors

There’s no bookkeeping table to leave behind here — DACPAC doesn’t keep a runtime history table the way Flyway or EF do. The whitelist still names your four tables; the extract is clean.

Now quench it back to prove the cast:

schemaquench --ConfigFile:quench.settings.json

The first run adopts your tables; the second is a clean no-op. That no-op is the proof: the package matches the live database your DACPAC built.

Where your publish profile used to carry the target and the deploy switches, the extract produced a Product.json that does the same job — and the switches move to the quench config:

// Product.json — the publish-profile's target identity:
{ "Name": "Shop", "Platform": "SqlServer",
  "ValidationScript": "SELECT CAST(CASE WHEN DB_ID('{{MainDb}}') IS NOT NULL THEN 1 ELSE 0 END AS BIT)" }

// quench config — where DropObjectsNotInSource lived:
{ "DropTablesRemovedFromProduct": true, "WhatIfONLY": false }

Your .sql files live in a template folder, the publish switches live in that config, and there’s no .dacpac build between commit and deploy.

What about pre/post-deploy scripts?

SSDT’s pre-deploy and post-deploy scripts — the escape hatch for data fixes, permission grants, anything the declarative model doesn’t cover — map straight onto SchemaSmith’s Before Scripts and After Scripts folders:

Package/Templates/Main/
  Before Scripts/
    Grant Deploy Role.sql           ← runs before the structural quench
  Tables/                           ← the declarative tables
  After Scripts/
    Backfill Region Codes.sql       ← runs once, tracked, after tables land

Same hook, same place in the deploy order. A script runs once per database (tracked in CompletedMigrationScripts); add [ALWAYS] to the filename for your old Script.PreDeployment.sql every-run behavior.

The part SSDT can’t do: a second engine

Here’s the limitation worth naming. The day your shop adds PostgreSQL or MySQL, your DACPAC investment doesn’t come along — there’s no DACPAC for those engines, and you’re back to picking a different tool and learning a different workflow for each one.

SchemaSmith’s answer isn’t a magic cross-engine package — schemas are native, so a SQL Server package is a SQL Server package, a PostgreSQL package is its own thing. What carries across is the workflow: the same schematongs extract, the same schemaquench deploy, the same declarative table files and Product.json shape, whichever engine you’re on. You keep your SQL Server package exactly as it is, and when Postgres shows up you author its package the same way — one toolset, one mental model, three engines. That’s the switcher’s payoff that DACPAC, by design, can’t offer.

Check yourself: Why is this the only Course 5 module that covers just one engine?

Because DACPAC is a SQL Server technology — there’s no DACPAC artifact for PostgreSQL or MySQL, so there’s nothing to migrate from on those engines. The other modules’ source tools (Flyway, Liquibase, EF Core, hand-rolled scripts) all run against all three engines, so those modules cover all three. SSDT/DACPAC is single-engine at the source, which is exactly the constraint that makes SchemaSmith’s one-workflow-many-engines story matter for a SQL Server shop planning to grow.

You were already declarative — SSDT saw to that. SchemaSmith just drops the build step and hands you tongs that work at any forge, not only this one. Cast the live database, set your publish switches in the quench config, and you’re deploying the same shape with one less tool in the way.

Got an SSDT project with a pile of pre-deploy scripts or a publish profile you’re not sure how to translate? Email me at forgebarrett@schemasmith.com — I read every one.

Next up: Course 5 · Module 5 — Migrating from hand-rolled scripts, the messiest “before” of all — a folder of ad-hoc SQL and a home-grown version table.

Until then, may nothing stand between your commit and the metal, and the craft you carry outlast any one forge.

— Forge

Mark this lesson complete