Dacpacs and Desired State: Deploy What You Mean, Not What You Remember

Shannon Lowder

Shannon Lowder

— 2 min read

A dacpac is a zip file. Inside it: your database schema compiled into a model, and optionally, your reference data. You pass it to SqlPackage.exe, point it at a target database, and SSDT figures out the delta and applies it. This is SSDT's deployment model, and it's worth understanding exactly how it works under the hood — because understanding it changes how you think about database deployments.

What's Inside a Dacpac

# Rename the .dacpac to .zip and unpack it
rename YourDatabase.dacpac YourDatabase.zip
expand-archive YourDatabase.zip -DestinationPath .\dacpac-contents

# Contents:
# model.xml          — the full schema model in XML
# Origin.xml         — source metadata (project name, SQL Server version target)
# [Content_Types].xml — packaging metadata

model.xml contains the complete schema definition: every table, every column with its exact data type and nullability, every index, every constraint, every view, every procedure — expressed in a normalized XML format that SSDT can compare against a live database.

The Comparison Process

When you run a dacpac deployment, SqlPackage.exe:

  1. Connects to the target database and reads its current schema
  2. Loads the dacpac's model
  3. Compares the two models, object by object
  4. Generates a SQL script representing the changes needed to bring the target into alignment with the dacpac
  5. Executes that script against the target database

Steps 1–4 can be run without step 5 — this is the "generate script only" mode, which lets you review what will happen before it happens.

# Generate the delta script without applying it
SqlPackage.exe /Action:Script   /SourceFile:YourDatabase.dacpac   /TargetConnectionString:"Server=yourserver;Database=YourDatabase;..."   /OutputPath:deployment_script.sql

# Review deployment_script.sql first, then apply
SqlPackage.exe /Action:Publish   /SourceFile:YourDatabase.dacpac   /TargetConnectionString:"Server=yourserver;Database=YourDatabase;..."

Always generate the script and review it before applying to production. The comparison model is reliable, but you should understand what's going to happen before it happens.

Destructive Change Detection

SSDT blocks certain destructive changes by default. Dropping a table, dropping a column, changing a data type in a way that requires data conversion — these generate warnings or errors unless you explicitly override the block. This is the right behavior: the tool is telling you "this change will lose data, are you sure?"

The blocks are configured in the publish profile (a .publish.xml file):

<!-- Publish profile: development.publish.xml -->
<Project ToolsVersion="14.0">
  <PropertyGroup>
    <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
    <DropObjectsNotInSource>False</DropObjectsNotInSource>
    <GenerateSmartDefaults>True</GenerateSmartDefaults>
  </PropertyGroup>
</Project>

BlockOnPossibleDataLoss is the critical one. Keep it True in production publish profiles. The deployment fails loudly rather than silently destroying data.

DropObjectsNotInSource deserves special attention. If True, any object in the target database that doesn't exist in the dacpac will be dropped. This is correct if your SSDT project is truly the source of truth. It's catastrophic if you've been making objects in the database without adding them to the project. Audit first; set this to True only when you're confident the project is complete.

Dacpac vs Migration Script: The Fundamental Tradeoff

Migration-script-based tools (Flyway, Liquibase, DbUp) take the opposite approach: you write each change as an explicit migration script, and the tool tracks which scripts have been applied. The dacpac model infers what needs to change from the desired state; the migration model records what was changed explicitly.

The dacpac model wins for: keeping the schema readable and current (you always have the current state in the project files), deploying to fresh environments (one dacpac, no migration history to replay), and eliminating drift detection as a manual process.

The migration model wins for: data migrations (complex transforms the state comparison can't infer), cases where the exact SQL of each change matters for audit purposes, and teams that are already fluent in migration-based tools and don't want to switch.

I used dacpacs for structural schema changes and explicit scripts for data transformations. Both tools, appropriate roles. As always, I'm here to help.

Read more