How To Compile SQLProj Files Using Cmdline MSBuild… Errors Included!

I’m fully back into builds and Visual Studio and even unit testing using MSTest once again, and I’m enjoying it very much. But I’m still working with databases, though less in a DBA-capacity. I recently needed to build and deploy about 40 small database projects that were in 4 or 5 different database solutions. And I needed to do this several times a day, so compiling via Visual Studio would be a boring and tedious process. So to speed up the process I decided to write the build process in an MSBuild target file and call initiate the build process through PowerShell. The targets file was simple enough to put together.

<Project ToolsVersion="4.0"

<ProjectReference Include=".\**\*.sln" Exclude=".\**\DBSolutionToIgnore.sln"/>

<Target Name="Default">
<MSBuild Projects="@(ProjectReference)" Targets="Rebuild"/>


This would be saved in the root location of all the solution folders as “BuildAllDBProjects.targets.xml”. Then the PowerShell would be simple enough; just save the script to the same location so that we can make use of the $PSScriptRoot variable. Note also that I am using the 32bit MSBuild location (to use 64 bit alter “Framework” to “Framework64″… but remember SSDT is only 32bit!)

$msbuild = "C:\Windows\Microsoft.Net\Framework\v4.0.30319\MSBuild.exe"
$MsBuilExists = Test-Path $msbuild
If ($MsBuilExists -ne $true) {write-host "msbuild does not exist at this location. Install Visual Studio 2015 (Community Edition should be adequate)"}
$buildFile = $PSScriptRoot+"\BuildAllDBProjects.targets.xml"
& $msbuild $buildFile

Then run that and it’s all good right? Well, I thought so to, but then this error appeared.

 C:\Users\Richard\Source\db_.sqlproj(59,3): error MSB4019: 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.

Colour me confused? Well, the error points towards a line in the sqlproj file. So I unloaded the sqlproj in Visual Studio and opened up the file in xml editor to see the offending command.

<VisualStudioVersion Condition="'$(VisualStudioVersion)' == ''">11.0</VisualStudioVersion>
<!-- Default to the v11.0 targets path if the targets file for the current VS version is not found -->
<SSDTExists Condition="Exists('$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets')">True</SSDTExists>
<VisualStudioVersion Condition="'$(SSDTExists)' == ''">11.0</VisualStudioVersion>
<Import Condition="'$(SQLDBExtensionsRefPath)' != ''" Project="$(SQLDBExtensionsRefPath)\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
<Import Condition="'$(SQLDBExtensionsRefPath)' == ''" Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" />

So in case you were not already aware of this, a proj file is basically a MSBuild target file that tells MSBuild what to build. You can even see the properties set for each build configuration. And the lines that are causing an issue here are just some nasty business.

What is happening is that when a project is built in Visual Studio some targets that are external of the whole process that were installed as part of Visual Studio are used as part of the process. Obviously, when we run MSBuild via cmdline, we are not setting the parameter “VisualStudioVersion”, because this is pretty much a “headless” build. So the sqlproj file handles this by setting the parameter to 11.0, which is pretty horrible. Given that projects are created in Visual Studio, I would’ve felt that the version a project was created in would be baked in as the default, as opposed to just some random version number. At the very least,  a warning that a default version number has been set would come in useful, especially as this is 2016 now and the sqlproj files default to 2010 targets.

Anyway, we can include a workaround by passing in the VisualStudioVersion like so:

$msbuild = "C:\Windows\Microsoft.Net\Framework\v4.0.30319\MSBuild.exe"
$MsBuilExists = Test-Path $msbuild
If ($MsBuilExists -ne $true) {write-host "msbuild does not exist at this location. Install Visual Studio 2015 (Community Edition should be adequate)"}
$buildFile = $PSScriptRoot+"\BuildAllDBProjects.targets.xml"
$arg = "/p:VisualStudioVersion=14.0"
& $msbuild $buildFile $arg
Write-Host "Press any key to continue ..."
$x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")

If you’re still getting the error, double check that you can compile the solution in Visual Studio and ensure that you have SQL Server Data Tools installed on your machine.

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

One thought on “How To Compile SQLProj Files Using Cmdline MSBuild… Errors Included!”

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s