Visual Studio - Publish dacpac as NuGet package in so other DB Projects can reference the DB

Question

I'm working on setting up a new organization where we are currently using VS 2017 and creating many SQL Server (2016) DB projects.

I've set up a private NuGet server on Azure to be able to use packages in each new project and my ideal solution would be to publish the helper DB Stored Procs, User Defined Types, etc as separate Nuget packages and be able to import them separately for any new projects to keep everything separated, versioned, etc.

Reading online, I've seen it seems possible to publish dacpac packages to Nuget using .nuspec files, but I can't figure out neither how to do this nor, more specifically, if there's a way to do this directly from Visual Studio.

Any help / pointers would be amazing!!

Thanks!

Feoff

Seems like there may not be a solution to this yet - Just found this: developercommunity.visualstudio.com/idea/785063/… – Feoff

Answer 1

Visual Studio - Publish dacpac as NuGet package in so other DB Projects can reference the DB

Actually, as you think, Database project does not support Nuget so far in VS due to some reasons.

In fact, it is really convenient to use nuget in database project. I agree with you on this point.

Nuget does make it easy to import a dacpac file into a project(if it can be done), just by adding these to the xxx.nuspec file:

<files>
<file src="xxx\xxx.dacpac" target="content">
</files>

Then it will import such file by nuget automatically in the new project.

And there is someone else who also wants this feature.

Suggestion

You can suggest a feature on our User Voice Forum to report your thought. And you can also share the link here and anyone who is interested in it including us will vote for you so that it will cause Microsoft to attach great importance.

Avestan

Thanks for the answer, Perry. I do feel this is something definitely lacking in VS since it seems only logical that any project should have nuget ability and it also makes sense to me to be able to store useful schema, functions, DB objects etc in Nuget to be used in other projects. As I commented in the question, it seems the issue is already open, but thank you so much for your answer and clarifying.... I just hope this does get added soon!!! – Feoff

As for the user voice forum, would that be different from the already open issue I mentioned? developercommunity.visualstudio.com/idea/785063/… – Feoff

Sure. That is the same one. But I think another person suggested that it should attract Microsoft's attention. So I suggest you could add any comment under that link to get any progress about the issue so far. I will add a comment since I test this in my side. Since my answer is the right one so far, I suggest you could consider accept it. Thanks:) – Avestan

I did, but I do so wish there was a better answer :p - Thanks. – Feoff

Answer 2

The new Microsoft.Build.Sql SDK is available still in preview in May 2023. This SDK allows the SDK style .sqlproj to reference nuget packages for dacpac files. See docs for its functionality.

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build">
  <Sdk Name="Microsoft.Build.Sql" Version="0.1.9-preview" />
  <PropertyGroup>
    <Name>Demo</Name>
    <DSP>Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider</DSP>
    <ModelCollation>1033, CI</ModelCollation>
    <ProjectGuid>{f4a1051f-6dd5-4701-bd74-180f9008a16c}</ProjectGuid>
    <TargetDatabaseSet>True</TargetDatabaseSet>
    <TargetFrameworkVersion>v4.7.2</TargetFrameworkVersion>
    <TargetFrameworkProfile />
    <ProjectVersion>4.1</ProjectVersion>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.SqlServer.Dacpacs" Version="160.0.0">
      <SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
      <DatabaseVariableLiteralValue>master</DatabaseVariableLiteralValue>
      <DacpacName>master</DacpacName>
    </PackageReference>
  </ItemGroup>
</Project>

It restores this Microsoft.SqlServer.Dacpacs package from nuget. By inspecting this package, we can see the dacpacs are in tools folder.

To pack your own dacpac nuget, add a .nuspec file to root and include desired .dacpac into tools folder.

<?xml version="1.0" encoding="utf-8"?>
<package xmlns="http://schemas.microsoft.com/packaging/2010/07/nuspec.xsd">
    <metadata>
        ...
    </metadata>
    <files>
        <file src="Public\*.dacpac" target="tools" />
    </files>
</package>

Soy