MSBuild.Sdk.SqlProj

Latest Stable Release Latest Prerelease Downloads

Introduction

A MSBuild SDK that is capable of producing a SQL Server Data-Tier Application package (.dacpac) from a set of SQL scripts that can be subsequently deployed using the Microsoft.SqlPackage dotnet tool. It provides much of the same functionality as the SQL Server Data Tools .sqlproj project format, but is built on top of the new SDK-style projects that were first introduced in Visual Studio 2017.

If you're looking for a video introduction, please watch this dotnetFlix episode. For some more background on this project read the following blogposts:

Code of conduct

Please take a moment to familiarize yourself with the code of conduct for this repository.

Usage

The simplest way to get started is to install our templates with dotnet new using:

dotnet new install MSBuild.Sdk.SqlProj.Templates

You can then create a new project file using the following command:

dotnet new sqlproj

If you don't want to target the latest version of SQL Server, you can specify a version to target using the -s Sql<version> switch.

See How to determine the version, edition, and update level of SQL Server and its components to map from the SQL Server SKU name to the version number, e.g. SQL Server 2022 to 16.0.

See SqlServerVersion Enum to map from the SQL Server version number to the SqlServerVersion needed by the template and project file, e.g. 16.0 to Sql160.

dotnet new sqlproj -s Sql160

You should now have a project file with the following contents:

<Project Sdk="MSBuild.Sdk.SqlProj/4.0.0">
    <PropertyGroup>
        <TargetFramework>net10.0</TargetFramework>
        <SqlServerVersion>Sql160</SqlServerVersion>
        <RunSqlCodeAnalysis>True</RunSqlCodeAnalysis>
        <!-- For additional properties that can be set here, please refer to https://github.com/rr-wfm/MSBuild.Sdk.SqlProj#model-properties -->
    </PropertyGroup>

    <ItemGroup>
        <!-- These packages adds additional code analysis rules -->
        <!-- We recommend using these, but they can be removed if desired -->
        <PackageReference Include="ErikEJ.DacFX.SqlServer.Rules" Version="3.2.0">
          <PrivateAssets>all</PrivateAssets>
          <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
        </PackageReference>
        <PackageReference Include="ErikEJ.DacFX.TSQLSmellSCA" Version="3.0.0">
          <PrivateAssets>all</PrivateAssets>
          <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
        </PackageReference>
    </ItemGroup>

    <PropertyGroup>
        <!-- Refer to https://github.com/rr-wfm/MSBuild.Sdk.SqlProj#publishing-support for supported publishing options -->
    </PropertyGroup>
</Project>

Then run a dotnet build and you'll find a .dacpac file with the same name as your project file in the bin\Debug\net8.0 folder. If you want to change the name of the .dacpac file you can set the <TargetName> property in your project file to something else.

Note: For PackageReferences this SDK currently assumes that the .dacpac file has the same name as the package. If you plan to create a NuGet package out of your project (see below) then make sure that <TargetName> matches the ID of your package.

Editing the Project file

The project file can have extension .csproj or .fsproj.

All .sql files in the same directory as the project file, except for those in the Pre-Deployment and Post-Deployment folders, are added to the package by default.

To exclude files from the package, add a <Content Remove="Directory\File.sql" />.

If you are using Visual Studio, to make files excluded from the package appear in Solution Explorer (like scripts for Pre- and Post-Deployment), add <None Include="Directory\File.sql" />.

Wildcards are supported for all nodes (Content, None, etc.). For example, <None Include="Directory\**" />.

Use an existing database

If you want to initialize your project with scripted objects from an existing database, you can do so with the following command:

sqlpackage /Action:Extract /Properties:ExtractTarget=Flat /SourceConnectionString:"<connection_string>" /TargetFile:<target_new_folder>

For example:

sqlpackage /a:Extract /p:ExtractTarget=Flat /scs:"data source=.\SQLEXPRESS;initial catalog=Chinook;Trusted_Connection=true;encrypt=False" /tf:.\Tables

Note: The /tf parameter is currently required, and must refer to a non-existing folder.

You can read more about the sqlpackage /a:Extract command syntax on Microsoft Learn.

Item templates

To create database objects you can use the following item templates:

| Template | Command | Description | | --- | --- | --- | | table | dotnet new table -n <name> [-s <schema-name>] | Creates a new database table with the provided name | | view | dotnet new view -n <name> [-s <schema-name>] | Creates a new database view with the provided name | | sproc | dotnet new sproc -n <name> [-s <schema-name>] | Creates a new stored procedure with the provided name | | inlinefunc | dotnet new inlinefunc -n <name> [-s <schema-name>] | Creates a new inline function with the provided name | | tablefunc | dotnet new tablefunc -n <name> [-s <schema-name>] | Creates a new table-valued function with the provided name | | scalarfunc | dotnet new scalarfunc -n <name> [-s <schema-name>] | Creates a new scalar function with the provided name | | uddt | dotnet new uddt -n <name> [-s <schema-name>] | Creates a new user-defined data type with the provided name | | udtt | dotnet new udtt -n <name> [-s <schema-name>] | Creates a new user-defined table type with the provided name |

Note: You can use both the project template and the item templates directly from Visual Studio, via the File - New Project and Add New Item dialogs.

If you already have a SSDT (.sqlproj) project in your solution, you can keep that as a "companion" project in order to enjoy the Visual Studio designer experience, as described in this blog post.

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:

<Project Sdk="MSBuild.Sdk.SqlProj/4.0.0">
    <PropertyGroup>
        <TargetFramework>net10.0</TargetFramework>
        <RecoveryMode>Simple</RecoveryMode>
        <SqlServerVersion>SqlAzure</SqlServerVersion>
    </PropertyGroup>
</Project>

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. Treating warnings as errors can be optionally enabled by adding a property TreatTSqlWarningsAsErrors to the project file:

<Project Sdk="MSBuild.Sdk.SqlProj/4.0.0">
    <PropertyGroup>
        <TreatTSqlWarningsAsErrors>True</TreatTSqlWarningsAsErrors>
        ...
    </PropertyGroup>
</Project>

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

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

<Project Sdk="MSBuild.Sdk.SqlProj/4.0.0">
    <PropertyGroup>
        <SuppressTSqlWarnings>71558,71502</SuppressTSqlWarnings>
        <TreatTSqlWarningsAsErrors>True</TreatTSqlWarningsAsErrors>
        ...
    </PropertyGroup>
</Project>

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

<Project Sdk="MSBuild.Sdk.SqlProj/4.0.0">
    <PropertyGroup>
        ...
    </PropertyGroup>

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

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- 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:

<Project Sdk="MSBuild.Sdk.SqlProj/4.0.0">
    <PropertyGroup>

MSBuild.SDK.SqlProj

Install / Use

README