Building sqlproj projects in Visual Studio Code with dotnet

We have come a long way, when it comes to developing .NET applications regardless of operating system. A major player on this journey has been Visual Studio Code. By being interoparable with big brother Visual Studio, it has enabled non-window users to be .NET developers at level with Windows users.

But despite of being incredible versatile, Visual Studio Code never have been able to handle or compile SQL Server Projects a. k. a. sqlproj projects. Most of it has to do, it required Windows only toolset SSDT (SQL Server Data Tools) to build sqlproj projects. For me it has been annoying, as there often is a sqlproj in a Visual Studio solution. The available mssql plugin for Visual Studio Code, is no help, as it is a management plug-in.

Lately, an early release of a plugin to handle sqlproj projects was released for Azure Data Studio. It was build with dotnet and with a little bit of tweaking, it can be used with Visual Studio Code as well. So here is my solution to compile from Visual Studio Code(It should work on all platforms)

First, get the required files

We need the following files:

Microsoft.Data.SqlClient.dll
Microsoft.Data.Tools.Schema.Sql.dll
Microsoft.Data.Tools.Schema.SqlTasks.targets
Microsoft.Data.Tools.Schema.Tasks.Sql.dll
Microsoft.Data.Tools.Utilities.dll
Microsoft.SqlServer.Dac.Extensions.dll
Microsoft.SqlServer.Dac.dll
Microsoft.SqlServer.TransactSql.ScriptDom.dll
Microsoft.SqlServer.Types.dll
System.ComponentModel.Composition.dll
System.IO.Packaging.dll
SystemDacpacs

As an Azure Data Studio user, I did install the sqlproj plugin:

On my machine (Mac), the files can be found at. They might as well be found in the home directory of you platform, else you might have to search for them:

~/.azuredatastudio/extensions/microsoft.sql-database-projects-0.5.1/BuildDirectory

For me at least, this not an optimal place. I prefer, the files, are placed relatively within the project, so it is self-contained and can be moved around, without breaking things. So I copy the files into the sqlproj project folder.

Update the sqlproj file

In the sqlproj file find the lines (sorry for the cramped XML, but it can also be read on this gist: https://gist.github.com/ChristianHenrikReich/878d40d764b0932036a03caa5b245b9b ):

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

Replace the 2 lines with these 3 (again see the gist, for better readablity):

 <Import Condition=”’$(NetCoreBuild)’ == ‘true’” Project=”$(NETCoreTargetsPath)\Microsoft.Data.Tools.Schema.SqlTasks.targets”/>
<Import Condition=”’$(NetCoreBuild)’ != ‘true’ AND ‘$(SQLDBExtensionsRefPath)’ != ‘’” Project=”$(SQLDBExtensionsRefPath)\Microsoft.Data.Tools.Schema.SqlTasks.targets”/>
<Import Condition=”’$(NetCoreBuild)’ != ‘true’ AND ‘$(SQLDBExtensionsRefPath)’ == ‘’” Project=”$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets”/>

Also these sections most be added, last in the bottom above </project>(see the gist https://gist.github.com/ChristianHenrikReich/878d40d764b0932036a03caa5b245b9b for more clearity):

<ItemGroup>
   <PackageReference Condition=”’$(NetCoreBuild)’ == ‘true’” Include=”Microsoft.NETFramework.ReferenceAssemblies” Version=”1.0.0" PrivateAssets=”All”/>
</ItemGroup>
<Target Name=”AfterClean”>
    <Delete Files=”$(BaseIntermediateOutputPath)\project.assets.json”/>
</Target>

The changes should not affect previous uses of the file og project. CI/CD should continue working.

Update Microsoft.Data.Tools.Schema.SqlTasks.targets

Find Microsoft.Data.Tools.Schema.SqlTasks.targets file:

Around line 103 change from $(NETCoreTargetsPath):

To ./ like:

Update task.json

Find task.json

The changes we have made has all been something to with the variables NetCoreBuild and NETCoreTargetsPath. So we need to set then during build like this task example (see gist https://gist.github.com/ChristianHenrikReich/878d40d764b0932036a03caa5b245b9b for full and readable example of task.json):

{
    “label”: “build all”,
    “command”: “dotnet”,
    “type”: “process”,
    “args”: [
         “build”,
         “/property:GenerateFullPaths=true”,       
“/consoleloggerparameters:NoSummary”,
         “/p:NetCoreBuild=true”,        “/p:NETCoreTargetsPath=./microsoft.sql-database-projects-0.5.1/BuildDirectory”,
          ],
    “group”: {
         “kind”: “build”,
         “isDefault”: true
        },
    “problemMatcher”: “$msCompile”
}

Result:

Final thought

I guess, in the future, there will be a sqlproj plugin for Visual Studio Code as well, rendering this post obsolete. But it was great fun figuring out, and if can help you, it has been worth it.