45

I have many .sqlproj projects that need to be built on our build server. I don't want to install all of Visual Studio on the build server just so I can install SSDT to build these. How can I build .sqlproj projects without a full VS install?

Here's the raw error I get on the build server when trying to build without SSDT intstalled:

C:\MyProject\MyProj.sqlproj (4): The imported project "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v11.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found. Confirm that the path in the <Import> declaration is correct, and that the file exists on disk.
Daryl
  • 3,253
  • 4
  • 29
  • 39

5 Answers5

47

Answer: Microsoft now has an official NuGet package (see blog post).

Old answer, prior to August 2016; provided in case the NuGet package doesn't work for you:

  1. Install dacframework.msi (x86|x64)
  2. Install SQLDOM.MSI (x86|x64)
  3. Install SQLLS.MSI (x86|x64)
  4. Install SQLSysClrTypes.msi (x86|x64)
  5. Install SSDTBuildUtilities.msi (from the "Administrator Install Point" as setup in step 3 here)

Done!

Source: Headless MSBuild Support for SSDT (*.sqlproj) Projects.

Daryl
  • 3,253
  • 4
  • 29
  • 39
  • 1
    You will need the following after this procedure too. http://blogs.msdn.com/b/jenss/archive/2013/01/28/headless-build-can-make-you-scratch-your-head-part-1.aspx – Redeemed1 May 29 '13 at 09:17
  • 3
    Although the blog entry Daryl mentioned in his original (unedited) post is on a site no longer available, it may still be found via the Wayback machine at https://web.archive.org/web/20131209161705/http://sqlproj.com/index.php/2012/03/headless-msbuild-support-for-ssdt-sqlproj-projects/ – Cal Jacobson May 27 '16 at 19:38
  • 1
    Missing part of the 'New' procedure: Both SQLDBExtensionsRefPath and SqlServerRedistPath need to be set AND must NOT use relative paths, or the .targets file or the .Task.dll won't be found . – skolima Jan 04 '18 at 16:45
  • 2
    Do we reference `Microsoft.Data.Tools.Msbuild` package in the `Visual Studio Project` and change the `.sqlproj` to point to the `targets` file ? – BaltoStar Sep 27 '19 at 23:56
7

Microsoft SQL Server Data Tools: http://msdn.microsoft.com/en-us/data/hh297027

Install the tools on build machine to fix the problem.

Ievgen Martynov
  • 7,870
  • 8
  • 36
  • 52
  • Can someone corroborate this? Will it work for building a Visual Studio 2013 Database Project (*.sqlproj) targeting SQL Server 2012? – Michael12345 Mar 07 '14 at 02:09
  • I have database project using Visual Studio 2013 and SQL Server 2012. It works. I've read all related posts/blogs, like @Daryl's. However, I still can't get it work on my build server. I need run database test on build server as well. So I installed Visual Studio 2013 completely. – qxg May 21 '14 at 08:44
  • This will also need Visual Studio to be installed on the build box and may not be recommendable or even allowed in certain companies – user20358 Mar 11 '15 at 14:20
  • I do not remember details, but I didn't install studio. But that was 2 years ago - now Microsoft could change something – Ievgen Martynov Mar 25 '15 at 11:45
  • from https://msdn.microsoft.com/en-us/library/mt204009.aspx: "If you don't have Visual Studio installed on your machine, installing SSDT will also install a minimal “Integrated Shell” version of Visual Studio 2015. This version of Visual Studio is free to install and use on as many machines as you wish. It gives you all the SQL Server project types, plus SQL Server Object Explorer and other SQL tools experiences" – snautz Jan 25 '17 at 11:58
3

The Microsoft SQL Server Data Tools team has released a NuGet package named Microsoft.Data.Tools.Msbuild, which helps to build SQL Projects on build servers.

see : https://blogs.msdn.microsoft.com/ssdt/2016/08/22/releasing-ssdt-with-visual-studio-15-preview-4-and-introducing-ssdt-msbuild-nuget-package/

NuGet package : https://www.nuget.org/packages/Microsoft.Data.Tools.Msbuild/

Vincent
  • 687
  • 2
  • 6
  • 8
1

SSDT v12.0.50730.0 requires Visual Studio to be installed beforehand. I found the easiest solution was to install the bare minimum Visual Studio components which were downloaded from MSDN Subscriber downloads:

  • Visual Studio 2013 Isolated
  • Visual Studio 2013 Shell

Then SSDT installed fine.

I also used part of the solution outlined above.
* Install dacframework.msi * Install SQLDOM.MSI * Install SQLLS.MSI * Install SQLSysClrTypes.msi

I use MSBuild 12.0 to perform the build which is also available as a separate download.

John Tunnicliffe
  • 91
  • 3
0

I was having the exact same issue building a SQL Server project on an Azure DevOps CI/CD pipeline. None of the pre-built build tasks would work for me.

Some answers mention a NuGet package, but I am not sure how can I use it, because SQL Server projects do not allow to install NuGet packages.

I solved this by avoiding to add a SQL Server project to the solution.

I achieved this by using an MSBuild SDK, capable of producing a SQL Server Data-Tier Application package (.dacpac) from the set of SQL scripts. By adding this second project to the solution, I managed to continue taking advantage of linking the project to a live database through SQL Server Object Explorer on Visual Studio. I gave a more detailed explanation about my implementation in this answer.

ccoutinho
  • 3,308
  • 5
  • 39
  • 47