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

:+1: for dacpac support.

[aersamkull]

one should be able to nuget everything :blush:

[andyfenna]

:+1: 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 :blush:".

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 (https://github.com/OctopusDeploy/OctoPack/pull/65), and an issue for OctopusDeploy (https://github.com/OctopusDeploy/Issues/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. :+1:

[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).

[ExcaliburVT]

I was also shocked when I found that I could not use a Nuget within a Sql Project. I was trying to package in some T4 Templates so that we could easily share them among all the Sql Projects that the team would use in the future in addition to my intention to share .dacpacs for cross cutting concerns.

[ivanpointer]

We've converted to DbUp to get around this issue. I'm sure this would still be advantageous for SSDT, RedGate ReadyRoll, etc., but we'll probably be too invested in DbUp to make a move any time soon.

[maciejw]

this nuget and sqlproj thing has another side, nuget has one cool feature, that is customization and extending of build process, through build folder that has msbuild target files in it. this is of course, development dependency only, but it would be cool to add package from nuget gallery that adds data migrations to the project, like this I've made https://github.com/maciejw/dacpac-data-migrations/ now I have to add it manually, thankfully nuget restore works just fine with it. I'm sure people will do something cool with dacpac and msbuild also.

[sryabkov]

@harikmenon Do you have any updates on this feature?

Our scenario: we want to add a database reference to the SSDT database project A using a dacpac file created based on SSDT database project B (A -> B, A depends on B). We want the dacpac file for project B to come from a NuGet package.

So, similarly to C# projects, one should be able to create a database reference that points to a dacpac file residing within a NuGet package in some NuGet feed. Right now, it seems to be only possible to use file-based referenced to dacpac files:

A minimal implementation would be:
1) Enable "Manage NuGet packages" functionality for SSDT projects
2) Be able to restore referenced NuGet packages to the "packages" folder
3) We would then be able to reference the dacpac file (for project B) that is located in the "packages/MyCompany.projectB.x.y.z" folder

[sryabkov]

It looks like there is a similar, possibly duplicate request for the same issue: #637

[jcfiorenzano]

DACPAC SUPPORT!!!!! 👍

[bzuidgeest]

I have read through this thread and it is all about supporting dacpac, but what about sql server CLR. It is perfectly valid to add and reference to an .net assembly that has functions marked for SQL CLR in a SQL project. In fact the project template starts out with two already. For know I have to add a code project dummy to my solution to use nuget and than add references from the sql project to the package folder. This is not a good solution. Most are company internal sql function with high reuseability from a company nuget feed.

[Arturas-K]

Hi,

Just wondering is this new NuGet integration with msbuild that is coming in VS 2017 Update 1:
http://blog.nuget.org/20170316/NuGet-now-fully-integrated-into-MSBuild.html#what-about-other-project-types-that-are-not-net-core

Will this have any effect on sqlproj projects?

[mmajcica]

It will be really beneficial for Nuget to support the sqlproj type of projects. This will allow us to package the dacpacts and then restore them in the sqlproj as Database references (ArtifactReference). This will avoid unnecessary scripts and checking dacpacs in the source code.

[emragins]

Yes, please! I'm dealing with a situation of databases depending on databases and multiple applications sharing the same database(s) (at different "levels" of the database hierarchy, even). Every other idea I can come up with to attempt to handle this cleanly does not even come close.

Sadly, fixing the root cause of the database quagmire is not an option.

[andyfenna]

Please can we have an update regarding this feature, it was raised back in 2015. I have resulted in creating powershell scripts to create the nuget package via command line, surely there must be an easier and cleaner way to do it?

[jayslife]

👍 dacpac support.

[gameman15]

+1 I have a SQL server CLR project and not being able to use NuGet to manage packages for it is absurd.

[PartTimeLegend]

+1

[StingyJack]

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?

@harikmenon - In the years since this was originally opened, the ability to run install/uninstall powershell scripts with nuget packages and "solution level" packages has been removed, and along with it, the workaround to the lack of SSDT support.

There really needs to be some correction made to the gap that was present, but is now unbridgeable without resorting to means that are counter to the spirit of nuget packages ("hard to install nuget packages").

From a user perspective, this has entered "production affecting bug with no workaround" territory and scheduled new features should be getting a lesser priority in favor of correcting this gap (in packages.config, package ref not stable or complete yet).

[rohit21agrawal]

@zhili1208 you did some work for database projects...can you update/close this issue accordingly?

[zhili1208]

this is in plan, but we are blocked by SQL project team now

[rohit21agrawal]

@zhili1208 can you mark the bug appropriately and link it to the bug/task on SQL project team?

[StingyJack]

Does that team have a specific presence here on GH (or elsewhere)?

EDIT: They are either not paying attention to external communication, or the SQL team does not exist any longer.

[andrescolodrero]

Some news coming from this?
We have a couple of DataWarehouse projects, when databases are changing very often.

I ended adding nuspec files (to pack a dacpac into our internal artifactory system) in each sqlproj, so the build process will create the nuget and send it to artifactory.

The project that needs external reference will have also a package.config file, that contains a definition or the latest package containing the referenced dacpac.
A build step in local, will download and restore the DACPAC in SolutionDir/packages.

The problem is that developers need to reference manually to the new version of DACPAC ( i can have 2-3 versions a week) in the new folder: packages/mydb.1.1.34/mydb.dacpac.

So i added a powershell script in the build to copy the nuget restored pakage to packages/mydb/mydb.dacpac and the developer wont need to change all the time the reference.

Other way is to create 2 packages: one with static version number (1.0.0) used for DACPAC reference and other dynamic build number (2,1,4) for deployment. Both, created at the same time.
looking forward for a better solution!

[fenngineering]

Any update on this issue?

[huideaki]

Any update ?

[kev-ops]

Is there a ticket for this to track on? This feature is needed by many.

[rrelyea]

@zhili1208 let’s figure out a way for sql team to have external visibility on this. They own the work in their codebase if they want to enable this.

[StingyJack]

@rrelyea - doesn't the nuget client determine what target projects are supported? Wouldn't this be another tfm; sql2008r2, sql2014, etc?

I think this thread is mostly asking for plain "reference a dacpac" support, and not the whole shebang with SQL CLR and other stuff. The removing of ps1 (the workaround for installing it to the sql project) and modifiable content files (T4's were mentioned here as well) is happening in the nuget codebase that you own. In lieu of waiting around for the SQL team to find out how to use the internet to view this and every other request, perhaps you could address this by restoring these features (for private repo's and packages; filter them out for nuget.org if you dont want to be culpable for potential malicious behavior there).

[mmajcica]

@StingyJack indeed referencing other dacpac's via nuget will be somethings that will make my BI teams very happy.

[fhanggi]

Please enable nuget packages for sql projects. We have a shared dacpac file that is referenced in all sql projects. It's such a pain to update to the latest version of that shared dacpac. nuget would make that a lot easier. thank you.

[PaulSorauer]

This. Please. Yes. Just do it.

[Brunas]

+1

[JamesMBristow2]

+1
Visual Studio's behavior for consuming packages/artifacts should be uniform across project types, or you are just increasing support costs. Targeting a subset of project types for nuget features and backlogging/refusing to support other project types (how many years later?) promotes developer confusion and means architects/managers/leaders have to develop more SOP/support documentation.

[JeffCordes]

I may be missing something, There is a nuget package for the build tools required to build sqlproj projects on a build server. https://www.nuget.org/packages/Microsoft.Data.Tools.Msbuild/

but you can not add the nuget to a sqlproj?

[PaulSorauer]

I may be missing something, There is a nuget package for the build tools required to build sqlproj projects on a build server. https://www.nuget.org/packages/Microsoft.Data.Tools.Msbuild/

but you can not add the nuget to a sqlproj?

Hey Jeff

The NuGet package you're referring to is one way to get the required _deploy time_ binaries onto the build server. These are .dll's that can inspect your DACPAC's, connect to the target server and make the changes as appropriate. As such they're only required by a builder server in order to conduct a release of the artifact created by a SQLPROJ project.

This thread is about the ability that SQLPROJ projects have to support NuGet package references in the same way that other projects do. In my case I wanted to have a common base SQLPROJ that had security schemas and then inherit the resultant DACPAC in other SQLPROJ projects in order to utilise the common security setup across our 'deployable' database projects. If I have a DACPAC reference I can do that, and since NuGet is the preferred way to package to distro artifacts it makes sense to do it via NuGet.

The issue, as per this thread, is that SQLPROJ projects do not support NuGet.

Hope that makes it clear.

[Amith211]

+1

[fenngineering]

+1

[Condor2708]

+1

[StingyJack]

This thread is about the ability that SQLPROJ projects have to support NuGet package references in the same way that other projects do.

No PackageReference only format for me thanks, with its lack of install.ps1, idiotic/purist include-on-build content assets, and no xdt transforms. This needs to work as package.config does, so I can make packages that are as useful as possible and not leave every consumer of a package with extra steps to do just to make the package work for them.

[Danlewis3]

+1

[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?