Table of Contents

Project Configuration

Model properties

There are a lot of properties that can be set on the model in the resulting .dacpac file which can be influenced by setting those properties in the project file using the same name. For example, the snippet below sets the RecoveryMode property to Simple:

  <PropertyGroup>
    <TargetFramework>net10.0</TargetFramework>
    <RecoveryMode>Simple</RecoveryMode>
    <SqlServerVersion>SqlAzure</SqlServerVersion>
  </PropertyGroup>

Refer to the documentation for more details on the available properties. The SqlServerVersion property is also supported.

Note

If you are replacing an existing .sqlproj be sure to copy over any of these properties into the new project file.

Model compiler options

Like .sqlproj projects MSBuild.Sdk.SqlProj supports controlling T-SQL build errors and warnings by using MSBuild properties.

Treat warnings as errors

Treating warnings as errors can be optionally enabled by adding a property TreatTSqlWarningsAsErrors to the project file:

  <PropertyGroup>
    <TreatTSqlWarningsAsErrors>True</TreatTSqlWarningsAsErrors>
    ...
  </PropertyGroup>
Note

Alternatively, you can use TreatWarningsAsErrors instead of TreatTSqlWarningsAsErrors to apply the same effect.

Suppress warnings at the project level

To suppress specific warnings from being treated as errors, add a comma-separated list of warning codes to SuppressTSqlWarnings property in the project file:

  <PropertyGroup>
    <SuppressTSqlWarnings>71558,71502</SuppressTSqlWarnings>
    <TreatTSqlWarningsAsErrors>True</TreatTSqlWarningsAsErrors>
    ...
  </PropertyGroup>

Suppress warnings for a specific file

You can suppress warnings for a specific file by adding SuppressTSqlWarnings for this file:

  <PropertyGroup>
    ...
  </PropertyGroup>

  <ItemGroup>
    <Content Include="Procedures\csp_Test.sql">
      <SuppressTSqlWarnings>71502</SuppressTSqlWarnings>
    </Content>
  </ItemGroup>
Note

Warnings suppressed at the project level are always applied to every file in the project, regardless of what is configured at the file level.

Pre-plan scripts

A pre-plan script runs before DacFX reads the target database and creates the deployment plan. It can prepare the target for model comparison, for example by migrating data before changing a column to non-nullable.

Add one pre-plan script to the project with the PrePlan item:

  <ItemGroup>
    <PrePlan Include="Pre-Plan\Script.PrePlan.sql" />
  </ItemGroup>

Only one pre-plan script can be included in a project. To compose it from multiple files, use SQLCMD :r includes in the main script, just as with pre-deployment and post-deployment scripts:

:r .\Migrations\PrepareCustomer.sql
:r .\Migrations\PrepareOrders.sql

The pre-plan script is stored in the .dacpac and is excluded from the database model. Included files are also tracked as build inputs, so changing one rebuilds the package.

Important

Pre-plan scripts require DacFX or SqlPackage 170.5.96 or later when deploying the .dacpac. Older deployment tooling ignores the pre-plan script. During publish, DacFX executes and commits the script in a separate transaction before model comparison. Pre-plan statements must be valid inside a transaction, and scripts should be safe to run more than once.

Pre-deployment and post-deployment scripts

These scripts will be automatically executed when deploying the .dacpac to SQL Server.

To include these scripts into your .dacpac add the following to your .csproj:

Include deployment scripts

  <PropertyGroup>
    ...
  </PropertyGroup>

  <ItemGroup>
    <PostDeploy Include="Post-Deployment\Script.PostDeployment.sql" />
    <PreDeploy Include="Pre-Deployment\Script.PreDeployment.sql" />
  </ItemGroup>

It is important to note that scripts in the Pre-Deployment and Post-Deployment folders are excluded from the build process by default. This is because these scripts typically don't define database objects, such as tables and stored procedure, but perform other tasks that cannot be represented in the model. If these aren't excluded your build might break with a SQL46010 error. Instead, you should create a script file that includes all of those scripts using the :r <path-to-script>.sql syntax and then reference that script in your project file (as shown above).

Run scripts from referenced packages

When publishing directly to a database with dotnet publish /t:PublishDatabase, pre-deployment and post-deployment scripts from package references and project references are skipped by default. Set RunScriptsFromReferences to True to run them:

  <PropertyGroup>
    <RunScriptsFromReferences>True</RunScriptsFromReferences>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="MyDatabasePackage" Version="1.0.0" />
  </ItemGroup>

The referenced pre-deployment scripts run before the main .dacpac is deployed. The referenced post-deployment scripts run after the deployment succeeds; they are skipped if it fails. The main project's own pre-deployment and post-deployment scripts are part of its .dacpac and do not require this setting.

SqlPackage does not run scripts from referenced packages.

Database context for referenced scripts

When using dotnet publish /t:PublishDatabase with RunScriptsFromReferences=true:

Referenced script Starts in
Pre-deployment The authenticated login's default database, such as master
Post-deployment The target database (TargetDatabaseName), after a successful deployment

This applies whether the target already exists or is being created. $(DatabaseName) expands to the target's name but does not switch the current database.

Place work that requires a newly created target in a post-deployment script. Pre-deployment work on an existing target should check that it exists and explicitly select it with USE or qualify object names with the database name.

SQLCMD variables

Especially when using pre-deployment and post-deployment scripts, but also in other scenarios, it might be useful to define variables that can be controlled at deployment time. This is supported using SQLCMD variables. These variables can be defined in your project file using the following syntax:

Define SQLCMD variables

  <ItemGroup>
    <SqlCmdVariable Include="MySqlCmdVariable">
      <DefaultValue>DefaultValue</DefaultValue>
      <Value>$(SqlCmdVar__1)</Value>
    </SqlCmdVariable>
    <SqlCmdVariable Include="MySqlCmdVariable2">
      <DefaultValue>DefaultValue</DefaultValue>
      <Value>$(SqlCmdVar__2)</Value>
    </SqlCmdVariable>
  </ItemGroup>
Note

With version 3.0.0 of the SDK, the DefaultValue is not applied to the build output, in line with the standard .sqlproj behaviour.

Edit this page