Support for SQL Server Database Tools projects

[karaken12]

(Moved from http://nuget.codeplex.com/workitem/2439)

SSDT projects can reference DacPac files, which are similar in concept to assemblies. There is a perceived need for NuGet packages that can install DacPacs into SSDT projects, so that common database code can be shared, e.g. throughout an organisation. SSDT is a core part of VS 2012, so it is likely that more people will be using these project types as time goes on.

Source for NuGet with SSDT project support currently on CodePlex at Karaken12/SsdtProjectSupport. Will move this to Github when I can.

[deepakaravindr]

Thanks for your suggestion! At this point, we don't plan to add support for DacPac in NuGet. This is something we might consider in the future

[ghost]

@karaken12 Are you still working on your fork?

[Thieum]

👍 for dacpac support.

[aersam]

one should be able to nuget everything 😊

[andyfenna]

👍 for dacpac support.

[Thieum]

For reference, SSDT documentation mentions using dacpac in nuget packages, but only as binaries : https://blogs.msdn.microsoft.com/ssdt/2016/04/06/sqldb-cicd-part3-nuget-db/

[andyfenna]

@Thieum this is for Azure SQL Server DBs & TFS, sqlproj's are not yet support by visual studio

[Thieum]

@andyfenna Totally agree, I wanted to point out that other tools are actually taking advantage of dacpac inside a nuget package, validating the use case.

[andyfenna]

Is this ever going to get done?

[ivanpointer]

I'm building CI tools for our team, and am hooking a part of the build process straight into the .csproj files, to copy the build artifacts out to a staging directory where the larger CI script then finishes transforms/packaging etc., before the packages will be shipped off for deployment.

While I am using ReadyRoll and Octopack, I'm building our scripts as a single headless command (the "Integrate Button"), with a tenant to minimize dependency on external tools; the scripts are being built to create simple ZIP archives that can be manually deployed by our support team.

Being able to install NuGet packages into .sqlproj projects (and .dbproj) will take the NuGet solution to the next level, with the Continuous Integration problem, as Continuous Database Integration is a critical part of an effective CI/CD solution for a modern, and agile team.

Ignoring the need for the DacPac files (it should be irrelevant what kind of files they are, given PowerShell, and the phenomenal design of content delivery via NuGet), we need this! Seriousally, all I need to do is to be able to "Manage NuGet Packages" for my .sqlproj and .dbproj projects; I just need a consistent way to deliver our MSBuild scripts, and other build artifacts, into scope of the project, and to modify the .sqlproj/.dbproj automatically, to import the MSBuild targets file!

Please revive this, for the simple fact that we need consistency across the solutions to our common problems! To quote aersamkull - "one should be able to nuget everything 😊".

Ivan Pointer
Director of Development
Oregon Information Division of NICUSA, Inc.

[harikmenon]

While I have re-opened this issue, this is just to encourage discussions on the topic so we can gather all the feedback. We are pretty full for the next 2 milestones (untill fall), so even if we decide to do it this probably wont happen this year.

[harikmenon]

Another question. Are you able to use nuget.exe install to get around this? Not sure if this going to work but curious to see if you have tried it?

[ivanpointer]

First, thank you for bringing this back to life Harikrishna! I am very grateful!

I haven't yet considered leveraging nuget.exe directly; this may work. To help shed some light on my situation, here's what I'm depending on the NuGet package to do:

1. Deliver the binaries needed to perform the single headless command build. These include the MSBuild Extensions Pack (Thank you Sayed Hashimi and others), FxCop, 7zip, and others. - I don't want the build server to have to download anything from anywhere to perform the build, I want all artifacts in place as soon as it comes down off TFS - not even a call out to a NuGet server to pull down packages.
2. Deliver the CI build script itself, which handles the inspections, testing etc. Based on the model suggested by Paul Duvall in his book "Continuous Integration: Improving Software Quality and Reducing Risk." (I really, really suggest this book for anyone considering implementing CI for their shop.)
3. Via "install.ps1", move the above binaries and scripts to a directory sister to the Visual Studio solution, then add the files to TFS (but not commit, just "add"), to massage the main script, based on some checks of the directory structure (we've got different directory structures, based on when, and by whom, the project was created). Finally, the customized main script, is opened in the Visual Studio editor, for the developers visibility/awareness.
4. Here's the biggest rub: Modify the .csproj/.sqlproj/.dbproj file to import a targets file, which extends "BuildDependsOn" to determine the release number from AssemblyInfo.cs, and to copy the built artifacts out to a separate staging area for later processing by the higher CI script (when being compiled via the CI script, and not VS), and to process the web/app config transforms, so that we have a ready to deploy package based on the build configuration (which we setup per environment).
5. The "uninstall.ps1" script is leveraged to reverse any changes to *.csproj etc., and remove the continuous integration build artifacts.

What I'm aiming for is an experience much like OctoPack, in that adding the NuGet package to the project is what causes it to be packaged for deploy. Right now, I'm focusing on just producing zip files, but I'd already listed OctoPack as a dependency of our NuGet package, and wired in the appropriate properties to get OctoPack to generate the *.nupkg files on build.

Wonderfully enough, ReadyRoll has options for generating Octopus Deploy artifacts on build, and even specifying build numbers. Not only is it dangerous to become too beholden to a "proprietary" tool such as ReadyRoll, I really want to minimize the number of tools that our developers need to interface with, to maximize the chances of success for the process/procedure.

Another option I'm strongly considering is a VSIX to handle all this, but my ultimate preference is to solve this with NuGet, as I believe the flexibility and robustness, and ultimately, the simplicity of the tool lends itself better to the problem I'm trying to solve. Perhaps a better solution may be to allow installation of packages marked as a "Development Dependency", on any visual studio project, as I believe that this issue is of a larger scope than just ReadyRoll/Database projects. I've not yet worked with other project types, but I'd imagine there are other project types that could stand to benefit from CI, the granddaddy of Agile software development.

I'll keep checking in, and update you with any significant findings/progress I have.

Thanks again,
Ivan

[ivanpointer]

Looking through the chain of items associated with this one, I've found that a PR was opened for OctoPack to handle this (OctopusDeploy/OctoPack#65), and an issue for OctopusDeploy (OctopusDeploy/Issues#1594), but I think what we really need here is simply the ability to add NuGet packages to DB/SQL projects, and I think this is really a Visual Studio/NuGet thing. If we fix this here, it'll fix the problem for OctoPack and OctopusDeploy.

Also, DbUp has caught my eye - it seems simpler than a lot of the competition, and being a console app, doesn't suffer the same issue.

I'm continuing to look for angles to this.

Thanks,
Ivan

[wegged]

I dont know if there is a way to upvote this but this is something we are sorely missing as well. This is a real necessity for CI especially if you dont want to commit dacpacs into GIT/Source control.

[Hoplo]

I'm very keen on being able to ad NuGet packages to .sqlproj projects. We generate internal NuGet packages for a product that get installed into different solutions. One package (just been created) holds a set of SQL files and the generated assembly from the product database project. This is mainly to help make DB packaging deployments a lot smoother and less error prone.

So for CI/CR/DevOps processes, and as @ivanpointer states for simple consistency in VS, it would great to have this support added.

Cheers,

Adrian

[czb182]

This would be useful for myself as well. My use case is to share dacpac dependencies for composite ssdt projects. We have a main sql server project which generates a dacpac and this dacpac will be referenced by many other sql server projects that each own a part of the larger database. Right now I would have to use git submodules to load in main sql server project into each repo or copy dacpac file manually everywhere its used after an update.

[ChristopherHaws]

I would really like to see this functionality as well. Currently we are packing the dacpac's as content and using a powershell script to add the reference to the sqlproj file. Would love to see this become a first class citizen. 👍

[andyfenna]

@ChristopherHaws this is exactly how we do it, it would be so much easier and fluent if this functionality was added

[brettwgreen]

I'm interested in using DACPACs in Nuget to support an empty-database testing package that my applications can use. The idea is to pull the latest dacpac through a nuget dependency, deploy it to localdb, then run your tests against this instance. Having the dacpac nuget-able would greatly simplify pulling in this dependency to the testing project.

[andyfenna]

Does anyone have an update on this?

[mikebrown0]

This should definitely be added.

I've spent the last few days converting our development framework from using git submodules to nuget packages and I just got to the modules that have SQL objects that go with them. I was a bit surprised when I found out I couldn't add a nuget package to an SSDT project... it's so natural in Visual Studio I just assumed it was supported. I think it's a common scenario to have a set of SQL objects and some code that need to move together as a unit between projects, especially when you get into large reusable components (for instance, I'm currently trying to nuget-ify our security module along with the SQL objects that code is responsible for).

[ldsenow]

+1000

[philippk80]

We need this in our company too. 👍

[LeandroNMedina]

In my company we use extensively this feature but we are stacked in VS 2013 that actually cover it. Fix it please!.

[Blacketik]

+1

[mheptinstall]

Is this a NuGet or SSDT/Visual Studio issue or both? I have suggested this feature for Visual Studio as I couldn't find an existing one:

https://developercommunity.visualstudio.com/idea/785063/nuget-database-references-in-ssdt-database-project.html

[Condor2708]

I hope 2020 and nuget give us the gift of this functionality....

[jmezach]

I did some experiments yesterday building a custom MSBuild SDK that's capable of producing a DACPAC. I did that using a command line tool that takes a bunch of SQL files as input and uses the public TSqlModel to build a model and then writes it to a DACPAC.

That was going quite well and I was even able to install NuGet packages into such a project. However I hit a bit of a blocking issue because it looks like the public model API doesn't support references to other DACPAC's at the moment (see Microsoft/DACExtensions#39).

[jmezach]

So I managed to work around the issue I mentioned earlier and think I've managed to create an MSBuild SDK that provides much of the functionality requested here while sacrificing some features of SSDT. Check out my announcement blog post and the associated repository and NuGet Package. Feedback is more than welcome.

[bzuidgeest]

@jmezach SQLCLR is very valuable to us (my and my colleagues), so though interesting solution this solves nothing for us. (for example CLR is the easiest route I know to get regex support in SQL which can be very usefull)

[jmezach]

@bzuidgeest I guess with some work SQLCLR could be supported as well, it's just that I didn't want to spent time on it if we weren't using it. Feel free to file an issue here. If there's enough demand for it I can consider adding it. Or perhaps someone could send a PR ;).

[Condor2708]

I can't believe that we are in 2020 and there is no solution to sqlproj using nuget references.

[StingyJack]

@heng-liu what is the Pipeline:Icebox tag supposed to indicate?

[heng-liu]

Hi @StingyJack , sorry for confusion caused, but the Pipeline label is a newly added label for internal use.

[jmezach]

@Condor2708 Have a look at MSBuild.Sdk.SqlProj

[Condor2708]

@Condor2708 Have a look at MSBuild.Sdk.SqlProj

Hi @jmezach ,
In the company i am working the whole datbase build is working with sqlproj and i think it is not possible to change it.
I was playing around with the MSBuild.Sdk.SqlProj and it is a very good library.

The solution I am working on is using MSBuild:

• I have two tasks one to create dacpacs with Nuget v3 core libraries and one used to Install dacapcs (this post helps me a lot understanding nuget [https://martinbjorkstrom.com/posts/2018-09-19-revisiting-nuget-client-libraries]

I have created one task that receives a dacpac.props file where I load the dacpac dependencies.

DACPAC PROS

<Project>
  <ItemGroup>
    <Dacpac Include="AddressManager.Database" Version="2.4.0" TargetFramework="net472"/>
    <Dacpac Include="EPC.Database" Version="2.4.0" TargetFramework="net472"/>
  </ItemGroup>
</Project>

sqlproj

  <UsingTask TaskName="InstallDacpacsTask" AssemblyFile="..\packages\DacpacBuilder.1.0.0.0\Dacpac.Builder.dll" />
  <Target Name="InstallDacpacs" BeforeTargets="BeforeBuild">
    <InstallDacpacsTask Dacpacs="@(Dacpac)" OutputFolder="$(MSBuildProjectDirectory)\Dacpacs" />
  </Target>
  <Target Name="ReferenceDacpacs" BeforeTargets="BeforeBuild" DependsOnTargets="InstallDacpacs">
    <ItemGroup>
      <ArtifactReference Include="Dacpacs\%(Dacpac.Identity).%(Dacpac.Version)\tools\%(Dacpac.Identity).dacpac">
        <HintPath>Dacpacs\%(Dacpac.Identity).%(Dacpac.Version)\tools\%(Dacpac.Identity).dacpac</HintPath>
        <SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
      </ArtifactReference>
    </ItemGroup>
  </Target>

[leus]

Any roadmap updates for this?

[lochnar187]

So, NuGet Package Manager still isn't supported in database projects?! This (NuGet) is a fundamental tool in VS and should be usable in EVERY Visual Studio project type! Consistency! Consistency! Consistency! It's key.

After having this reported in VS 2015 and here we are (a good six years later) in 2021 and this is still a thing? Someone's got questionable engineering/time management skills...

[jmezach]

@lochnar187 Please have a look at MSBuild.Sdk.SqlProj which gets you most of the way.

[lochnar187]

@lochnar187 Please have a look at MSBuild.Sdk.SqlProj which gets you most of the way.

A step, but still of no help with a VS db project building CLRs.

[nkolev92]

Team Triage:

The work that would be required here needs to be done by a different component, the SQL server Database Tools projects and not NuGet.

Please upvote and follow the following issue in the DacFx repo, microsoft/DacFx#124.

[Adam-S-Daniel]

FYI, @nkolev92: microsoft/DacFx#124 (comment)

I suggest renaming this issue to correspond to this ticket on Developer Community: “On Roadmap” since 2020: NuGet database references in SSDT database projects. I agree with @Coder3333 that the current name implies a feature that already exists: direct references to DacPac files, akin to the bad old days when one might add a reference to a DLL as opposed to achieving the same through a NuGet dependency.

@dzsquared, might it make sense for your team to reach out to @nkolev for the NuGet team’s thinking/guidance on this? NuGet/Home#545 (comment)

Team Triage:
The work that would be required here needs to be done by a different component, the SQL server Database Tools projects and not NuGet.
Please upvote and follow the following issue in the DacFx repo, microsoft/DacFx#124.

[dzsquared]

This feature is now in preview with the Microsoft.Build.Sql SDK for SQL projects - https://github.com/microsoft/DacFx/blob/main/src/Microsoft.Build.Sql/docs/Functionality.md#package-references