How to build .sqlproj projects on a build server?

Question

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.

Caldarium

Answer 1

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.

Caldarium

You will need the following after this procedure too. blogs.msdn.com/b/jenss/archive/2013/01/28/… – Boorman

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 web.archive.org/web/20131209161705/http://sqlproj.com/index.php/… – Castrate

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 . – Unbacked

Do we reference Microsoft.Data.Tools.Msbuild package in the Visual Studio Project and change the .sqlproj to point to the targets file ? – Discommodity

Answer 2

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

Install the tools on build machine to fix the problem.

Width

Can someone corroborate this? Will it work for building a Visual Studio 2013 Database Project (*.sqlproj) targeting SQL Server 2012? – Curlicue

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. – Lorileelorilyn

This will also need Visual Studio to be installed on the build box and may not be recommendable or even allowed in certain companies – Libelant

I do not remember details, but I didn't install studio. But that was 2 years ago - now Microsoft could change something – Width

from 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" – Untouchable

Answer 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/

Cofield

Answer 4

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.

Namecalling

Answer 5

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.

Chayachayote