Table of Contents

References

Package references

MSBuild.Sdk.SqlProj supports referencing NuGet packages that contain .dacpac packages. These can be referenced by using the PackageReference format familiar to .NET developers. They can also be installed through the NuGet Package Manager in Visual Studio.

Basic package references

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

It will assume that the .dacpac file is inside the tools folder of the referenced package and that it has the same name as the NuGet package. Referenced packages that do not adhere to this convention will be silently ignored. However, you have the ability to override this convention by using the DacpacName attribute on the PackageReference. For example:

Override the dacpac name

  <ItemGroup>
    <PackageReference Include="MyDatabasePackage" Version="1.1.0" DacpacName="SomeOtherDacpac" />
  </ItemGroup>

This will add a reference to the tools\SomeOtherDacpac.dacpac file inside the MyDatabasePackage package. Note that if that file doesn't exist within the package, the package reference will still be silently ignored. However, the build will most likely fail if your project actually references objects from the reference package.

By default, the package reference is treated as being part of the same database. For example, if the reference package contains a .dacpac that has a table and a stored procedure and you would dotnet publish the project the table and stored procedure from that package will be deployed along with the contents of your project to the same database. If this is not desired, you can add the DatabaseVariableLiteralValue item metadata to the PackageReference specifying a different database name:

Reference another database

  <ItemGroup>
    <PackageReference Include="MyDatabasePackage" Version="1.1.0" DatabaseVariableLiteralValue="SomeOtherDatabase" />
  </ItemGroup>

In this scenario you can access the objects defined by MyDatabasePackage by using the [SomeOtherDatabase].[<schema>].[<object>] syntax.

You can also use SQLCMD variables to set references, similar to the behavior of a .sqlproj project, by adding DatabaseSqlCmdVariable and optionally ServerSqlCmdVariable item metadata to the PackageReference element:

Note

Don't forget to define appropriate SQLCMD variables

Use SQLCMD variables

  <ItemGroup>
    <PackageReference Include="MyDatabasePackage" Version="1.1.0" DatabaseSqlCmdVariable="SomeOtherDatabase" ServerSqlCmdVariable="SomeOtherServer"/>
  </ItemGroup>

  <ItemGroup>
    <SqlCmdVariable Include="SomeOtherDatabase">
      <DefaultValue>OtherDatabase</DefaultValue>
      <Value>$(SqlCmdVar__1)</Value>
    </SqlCmdVariable>
    <SqlCmdVariable Include="SomeOtherServer">
      <DefaultValue>OtherServer</DefaultValue>
      <Value>$(SqlCmdVar__2)</Value>
    </SqlCmdVariable>
  </ItemGroup>

In this scenario you can access the objects defined by MyDatabasePackage by using the [$(SomeOtherServer)].[$(SomeOtherDatabase)].[<schema>].[<object>] syntax. Also you can combine ServerSqlCmdVariable with DatabaseVariableLiteralValue and use [$(SomeOtherServer)].[SomeOtherDatabase].[<schema>].[<object>] syntax.

When deploying a dacpac with references to other dacpacs, if you want the contents of all dacpacs to be deployed to a single database you will need to specify the IncludeCompositeObjects property. For example:

Deploy composite objects

sqlpackage
    /Action:Publish \
    /SourceFile:MyDatabase.dacpac \
    /TargetServerName:localhost \
    /TargetDatabaseName:MyDatabase \
    /TargetUser:sa \
    /TargetPassword: MyP@ssword \
    /Properties:IncludeCompositeObjects=True

Project references

Similar to package references you can also reference another project by using a ProjectReference. These references can be added manually to the project file or they can be added through Visual Studio. For example, consider the following example:

Basic project references

  <ItemGroup>
    <ProjectReference Include="../MyOtherProject/MyOtherProject.csproj" />
  </ItemGroup>

This will ensure that MyOtherProject is built first and the resulting .dacpac will be referenced by this project. This means you can use the objects defined in the other project within the scope of this project. If the other project is representing an entirely different database, you can also use DatabaseVariableLiteralValue or SQLCMD variables on the ProjectReference similar to PackageReference:

Reference another database

  <ItemGroup>
    <ProjectReference Include="../MyOtherProject/MyOtherProject.csproj" DatabaseVariableLiteralValue="SomeOtherDatabase" />
  </ItemGroup>
  <ItemGroup>
    <ProjectReference Include="../MySecondProject/MySecondProject.csproj" DatabaseSqlCmdVariable="SecondOtherDatabase" ServerSqlCmdVariable="SomeOtherServer" />
  </ItemGroup>

  <ItemGroup>
    <SqlCmdVariable Include="SecondOtherDatabase">
      <DefaultValue>SecondDatabase</DefaultValue>
      <Value>$(SqlCmdVar__1)</Value>
    </SqlCmdVariable>
    <SqlCmdVariable Include="SomeOtherServer">
      <DefaultValue>OtherServer</DefaultValue>
      <Value>$(SqlCmdVar__2)</Value>
    </SqlCmdVariable>
  </ItemGroup>
Note

We do not support adding a ProjectReference to an existing .sqlproj file.

Direct DACPAC file references

Reference an existing .dacpac file without a NuGet package or another project:

<ItemGroup>
  <ArtifactReference Include="../Database artifacts/Shared.dacpac" />
</ItemGroup>

Include is the DACPAC path, absolute or relative to the consuming project directory. Paths may contain spaces. The file must already exist when references are resolved; a missing file fails the build even if compilation would otherwise be skipped. Use a project reference when the dependency should be built automatically.

HintPath is optional and supports migrating the reference syntax used by original .sqlproj projects. New references only need Include. If a nonempty HintPath is present, it supplies the file path instead of Include; an absent or empty value uses Include.

Database mapping and dependency suppression use the same metadata as package and project references. See referencing another database, SQLCMD variables, and SuppressMissingDependenciesErrors; use ArtifactReference in place of PackageReference in those examples. Missing-dependency suppression does not suppress missing-file errors. For deployment to the same database, see deploying composite objects.

Referenced files are copied alongside the consuming DACPAC and to the output of projects that reference it. To disable copying for a reference:

<ArtifactReference Include="references/Shared.dacpac" Private="false" />

Omitting Private or setting it to true enables copying. Changing it to false does not remove copies left by previous builds. A newer referenced file triggers recompilation regardless of Private. Use -t:Rebuild when changing reference settings through command-line properties, because incremental compilation may not detect those changes.

Declare each dependency once, including across package, project, and direct references; duplicates are not automatically removed. Use distinct filenames because copied references share an output directory. Add direct references by editing the project file; Visual Studio reference-picker integration and automatic lookup of installed system DACPACs are not supported.

Referencing system databases

Microsoft has released NuGet packages containing the definitions of the master and msdb databases. This is useful if you want to reference objects from those databases within your own projects without getting warnings. To reference these, you'll need to use the DacpacName feature for package references described above. For example:

  <ItemGroup>
    <PackageReference Include="Microsoft.SqlServer.Dacpacs.Master" Version="160.2.8" DacpacName="master" DatabaseVariableLiteralValue="master" />
  </ItemGroup>

The above example references the master database from the Microsoft.SqlServer.Dacpacs.Master NuGet package. Please note that there are different major versions of that package for different versions of SQL Server. It is recommended to reference the most recent minor/patch version of the package as the SqlServerVersion you are targeting with your project, as seen in the example above.

For other variants of SQL Server / Azure SQL Database there are dedicated packages as listed here.

Circular References and SuppressMissingDependenciesErrors

In order to solve circular references between databases that may have been incorrectly set up, it is possible to add SuppressMissingDependenciesErrors to both Package References and Project References:

  <ItemGroup>
    <PackageReference Include="MyDatabasePackage" Version="1.1.0" DatabaseVariableLiteralValue="SomeDatabase" SuppressMissingDependenciesErrors="True"/>
  </ItemGroup>

  <ItemGroup>
    <ProjectReference Include="../MyOtherProject/MyOtherProject.csproj" DatabaseVariableLiteralValue="SomeOtherDatabase" SuppressMissingDependenciesErrors="True"/>
  </ItemGroup>
Edit this page