52

Closed

Support for SQL Server Database Tools projects

description

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.

file attachments

Closed Apr 30, 2015 at 10:06 PM by yishaigalatzer
We are not tracking bugs on this site for quite a while. Please file bugs on github.com/issue

comments

dotnetjunky wrote Jul 19, 2012 at 2:22 PM

If your code is complete and ready, feel free to send a pull request.

Karaken12 wrote Jul 19, 2012 at 2:45 PM

No, it's not complete yet! I opened this issue because the contributors docs suggest to do so.
By the way, is it right that I can't change the work item details? If so, could you change the type to feature? Thanks.

dotnetjunky wrote Jul 19, 2012 at 8:05 PM

done.

Swellenator wrote Aug 22, 2012 at 2:45 AM

This would be a very useful feature! I voted.

mbante wrote Feb 28, 2013 at 3:58 PM

What remaining work is required to get the pull request through?

Karaken12 wrote Mar 1, 2013 at 11:02 AM

@mbante There are a couple of design decisions that I'm not too certain about; there are more details in my latest post in the associated discussion thread.

Beyond that, I haven't done any testing with creating SSDT NuGet packages; it would certainly be nice to be able to spec from an SSDT project. What this really needs is people actually testing and using it, to iron out any bugs and try to see if there are other features that should be supported.

dotnetjunky wrote Mar 1, 2013 at 12:49 PM

Hi,

Thanks for the initiative and sorry for not replying to the thread. I'll reply to it later today.

marcinz wrote Apr 19, 2013 at 4:43 PM

It will be nice if we could have package-like behaviour in SSDT.

And there is one feature that DACPACKS lacks - dictionary data in tables.

Karaken12 wrote May 30, 2013 at 10:15 AM

@marcinz If by "package-like behaviour" you mean NuGet support, that's exactly what this is about! If you've got the time, please download and try out what's already been implemented, and see what you think. I'd love to get some feedback.

As for DACPACs, they do now support including table data. However, you cannot currently add data to an SSDT database project and have it compiled into a DACPAC. This may or may not change with future releases of the SSDT.

jmezach wrote Apr 15, 2014 at 8:34 AM

This is something that could be really useful for a project I'm working on. Is there anything I can do to help test this so that it can become part of the product?

Karaken12 wrote Apr 16, 2014 at 3:19 PM

@jmezach That'd be great -- in fact what this needs most is people using it to test it, flush any bugs out, and let me know what other changes it needs.
To get this going for your own use you'll need to download the source and compile it yourself -- that's not hard, you just need to download the Visual Studio SDK and there are plenty of resources out there telling you how to do it. I'd suggest you get rid of the "SSDT_HACK" compilation variable in VisualStudio.csproj, but leaving it in won't cause any problems that I know of.
Once you've got that compiled and installed (make sure you uninstall the regular NuGet beforehand, or they'll conflict) you can start using it!
Creating a NuGet package containing dacpac files is the same as creating one containing .dlls. If you need to reference the "master" database, include that as a frameworkAssembly; there are examples of nuspec files in the test/EndToEnd/packages/Ssdt* directories.

The most important thing to me is to get some feedback on this. Let me know what works, and what doesn't; what's easy, and what could be easier; what does this not do that you need it to?

Thanks a lot,
Tom

jmezach wrote Apr 17, 2014 at 8:47 PM

I took it for a spin and it already does pretty much what we need it to do. I did have to fix two issues in the NuGet command line tool. In both the NuGet.Common.ProjectHelper class and the NuGet.Commands.PackCommand class there is a list of supported extensions for project files which doesn't include the .sqlproj extension. Once I changed those I was able to build a NuGet package from one of our database projects and subsequently install it into another project.

I did notice though that at the moment using the pack command will create a NuGet package with a lib folder containing the SQLCLR assembly associated with the SSDT project. Installing such a package into a C# project will add that assembly to the project. At the moment we're not doing anything with SQLCLR, so we get an unnecessary reference to an empty assembly.

Also I was wondering how this would work when installing a package build from one SSDT project into another SSDT project. SSDT supports referencing another DACPAC package so you can build on top of that. It is however not something we need right now, but I can imagine that might become an interesting scenario for us in the future. Any thoughts on that?

Karaken12 wrote Apr 22, 2014 at 9:28 AM

@jmezach so are you using it to add content files (i.e. stored procedures, etc.) to your database projects? That's interesting, because I assumed that most people who would want to use NuGet with SSDT would want DACPAC references. So in answer to your question, yes, DACPAC references are definitely included, but there are still some design questions to be answered; if and when you start using this feature, please let me know what you think of it!

Nice to know it's working for you, with a few changes; can you let me have those modifications you made, so I can include them in the source? You can message me from my profile page.

I'll take a look at the empty assembly problem; I'm not quite sure how we can manage this without manual intervention, but there might be some way to deal with it automatically.

Thanks a lot, and please keep the feedback coming!
Tom

jmezach wrote Apr 22, 2014 at 1:18 PM

Actually, that is exactly what I was looking for, adding the DACPAC as a reference to the project when installing it into another SSDT project. But by default the .dacpac isn't included in the NuGet package by default when running nuget pack on the command line. I had to explicitly include it using the nuspec file. Therefore I was wondering how this would work. Also, when adding a dacpac as a reference to a project there are several choices that need to be made, so I was wondering how we would address those.

As to the changes I've made, I tried to sent you a private message but it said you didn't want to be contacted ;). So I attached a patch file here that contains my changes.

Karaken12 wrote Apr 22, 2014 at 3:18 PM

Thanks for that, and sorry about being un-contactable! I've changed my settings now, so hopefully it'll work in future. :-)

I've now committed your changes, as well as one that picks up .dacpac files from the output, so NuGet pack should work more as you'd expect now. Please update your local copy and see if that works any better.

You are correct that there are choices to be made with adding dacpac references. For now, I've just decided to add them in one specific way, with no other options. But this can be changed, and I've really been waiting for someone with a real use-case (e.g. you!) to finalise things. Let me know what you think of the current behaviour, what you want it to do, and what you think it should do (if they're not the same!).

Lastly, it seems that the .dll file will always be created by the SSDT build process, although it's effectively empty if you don't use SQLCLR. Unless we somehow include a way for NuGet to distinguish between an "empty" .dll and one with content, creating a package from an SSDT project will have to always include it for fear of excluding a "real" one. Of course, you can always create a .nuspec file manually, as you mentioned.

Best wishes,
Tom

jmezach wrote Apr 22, 2014 at 8:02 PM

I just took your changes for a spin and its looking good. I now get a nuget package that also contains the .dacpac next to the empty SQLCLR assembly. I then installed that NuGet package into another database project, which added a reference to the .dacpac as well as the empty SQLCLR assembly. Since that project already depended on things that were in the other .dacpac I recompiled the project to see if that would still succeed and it did. I then compared my project file against the unmodified version and except for the path to the .dacpac it was almost identical. I did notice that previously there was a SuppressMissingDependenciesErrors attribute on the reference with a value of False which was now gone. I believe that is the default anyway, so it probably doesn't matter. My guess here is that the .dacpac is being references as if it is part of the same database. Is that correct?

I then uninstalled the package from my project. This gave me some warnings, but they went by to quickly to actually see them. After uninstalling however the references to both the SQLCLR assembly and the .dacpac were still there. I then tried installing the package again, but this failed horribly. See the attached image. I'm guessing that removing the references fails because of the "'object' does not contain a definition for 'References'" and that that is also the reason why the first uninstall also didn't remove the references.

As for the empty SQLCLR assembly, I was thinking. Wouldn't it be possible to check the underlying project file for the presence of items that have Compile as the Build Action? If there aren't any we can conclude that the project doesn't containing any C# code and thus we can ignore the assembly all together. Otherwise we need to include it in the package.

Karaken12 wrote Apr 23, 2014 at 4:20 PM

For some reason I have never tried to uninstall! How strange. Anyway, I've updated the code so that should work now. More importantly, I've moved the SSDT 'references' hack once again, this time into ProjectExtensions.cs, and I've deleted the code that required the SSDT_HACK flag. Please give this a go, see if it works!

Yes, the DACPAC is being referenced as "same server, same database", which is what happens when you don't do anything else. If you need something other than that, we'll have to look into ways of doing that. (I'm actually working on a separate project which may help, but that's a fair way off being done.)

To be honest, I'm not going to spend too much time thinking about excluding the CLR project. It might be interesting to try to get that working, but given there's a usable workaround (i.e. write the .nuspec file manually) it's not high on my list of priorities. If you find it's something you can't live without then we'll have to revisit that assessment. :-)

jmezach wrote Apr 24, 2014 at 11:26 AM

I just tried the new version and installing and uninstalling works just fine now, so good job ;).

I do have another question though. One of the use cases I see is that we have a lot of different solutions that all use the same database. So what I would like to do is create a NuGet package from our database project (that is in yet another solution) and install that NuGet package into a solution that only contains .NET projects. I would then be able to include some PowerShell scripts into the package (which is already possible using a .nuspec file I believe) that would allow a developer to update or create the database right from the Package Manager Console.

However, I just tried to install an SSDT NuGet package into a WebAPI project but it fails to add the reference to the DACPAC. This makes sense of course, since a WebAPI project cannot reference a DACPAC directly, but I would like to have the DACPAC available in the packages folder for our PowerShell scripts to use. Does that make sense to you as well?

blakeman wrote May 20, 2014 at 10:19 PM

SSDT projects can reference .Net assemblies as well. Does this fork support adding NuGet packages with .Net dll's and adding them as references to be included in the publish to the database?

Karaken12 wrote May 21, 2014 at 9:16 AM

@blakeman
The short answer is: Yes.

The slightly longer answer is that you can add your own .NET dll's and GAC references in the same way as with any other project type. I haven't really played around with SQL CLR much, so I don't know what caveats there may be, but in theory you can add any regular NuGet package and it'll just work.

Karaken12 wrote May 21, 2014 at 9:37 AM

@blakeman
Just added Moq to an SSDT project, and that seemed to work just fine. Please let me know if you start using the fork, what you are doing with it, and how it's working for you!

blakeman wrote May 22, 2014 at 3:58 PM

I was able to add the package to my SSDT project and build successfully. When I publish I get an error complaining that my assembly was not found in the SQL catalog. Before converting this project over to using NuGet, the assemblies were listed under References, and in the Assemblies folder. Now the assemblies are only listed under References. I wonder if it can't find the dll's now that they are in a folder named after the package, instead of in the Assemblies folder. Were you able to publish your database after adding Moq?

Karaken12 wrote May 27, 2014 at 10:43 AM

@blakeman
Sorry, I wasn't really familiar with deploying SQLCLR to Sql Server, but I think I have it figured out now. As outlined in this Stack Overflow answer, you need to change a couple of options on the assembly reference: in the properties panel, change "Generate Sql Script" and "Model Aware" to true. If your assembly complies with Sql Server's definition of "safe" then that's all you need to do; run a Schema Compare or a deploy, and it should work just fine.

[For reference, I couldn't get this working with Moq, because it isn't Sql Server "safe", but I tried it with JPBetley.Tiny-DotNet and got it to deploy on my local Sql Server instance.]

So, a question for you: given your workflow, should (SSDT-enabled) NuGet do this for you? Will every NuGet package referenced this way need to be deployed to the server along with your own code? Will they always be "safe"? If not, should we attempt to set that property appropriately? Any thoughts you have (particularly about how you want to do it) would be very helpful. :-)

Cheers,
Tom

blakeman wrote May 29, 2014 at 3:55 PM

Thanks for the info, I've got it working now. I needed to set the Permission set to "External" to match our current deployment. I am of the opinion that every referenced assembly will be deployed to the server, but they won't always be "Safe". I don't think enough is known to be able to set that for them. The other two options: Generate Sql Script-> True and Model Aware- True should always be set.

-Brian

Karaken12 wrote Jun 5, 2014 at 2:39 PM

@Brian Uploaded the latest version which will now set those two attributes. It doesn't set anything for Permission Set, which just defaults to "Safe" anyway. It might be good to have some way to set the permission set in the nuspec file (if you expect the package to be installed into an SSDT project), but that can come later. Do give it a go, and let me know how well it works for you, having to set that one attribute.

dmccaffery wrote Sep 17, 2014 at 9:46 PM

How far off are you from submitting a pull request? Any other outstanding issues?

straubwa wrote Dec 29, 2014 at 2:50 PM

I'm interested in this feature, will there be a pull request?

alexjamesbrown wrote Apr 14, 2015 at 1:15 PM

all quiet on this? any update expected?

Karaken12 wrote May 7, 2015 at 12:50 PM

Replacement issue created on GitHub: https://github.com/NuGet/Home/issues/545