Build sqlproj on Azure DevOps

Question

I'm trying to use Azure DevOps Pipelines to build my .NET Core 2.1 solution from GitHub. It includes a SQL project that has a TargetFrameworkVersion of v4.6.2. This project always fails to build.

Build FAILED.

/home/vsts/work/1/s/MySolution/MyDatabase/MyDatabase.sqlproj : warning NU1503: Skipping restore for project '/home/vsts/work/1/s/MySolution/MyDatabase/MyDatabase.sqlproj'. The project file may be invalid or missing targets required for restore. [/home/vsts/work/1/s/MySolution/MySolution.sln]
/home/vsts/work/1/s/MySolution/MyDatabase/MyDatabase.sqlproj(57,3): error MSB4019: The imported project "/usr/share/dotnet/sdk/2.1.403/Microsoft/VisualStudio/v15.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.
1 Warning(s)
1 Error(s)

How do I reference or include those targets for the build server? It builds fine in VS2017. I've spent more than a day hunting and cannot find any information on this problem.

Velamen

What kind of build agent are you using? Windows or Linux? How do you build your solution, do you've some sample yaml code to show us? – Fridge

Thanks, that comment alone showed me the answer! – Velamen

Answer 1

Thanks to Herman Cordes for directing my investigation.

The problem was the selected build server. SSDT is a Windows-only package, so I had to use a Windows VM instead of the default Ubuntu VM, and use the VSBuild@1 task instead of DotNetCoreCLI@2.

azure-pipelines.yml

pool:
  vmImage: 'vs2017-win2016'

steps:
- task: VSBuild@1
  displayName: 'vsbuild $(buildConfiguration)'
  inputs:
    configuration: $(buildConfiguration)

EDIT: The MSBuild@1 task also works.

Velamen

I have been using the VSBuild@1 on a windows2019 agent and I am getting the exact same error – Impaste

Answer 2

Here is a solution for linux build agent Easiest way to build DacPac file on a linux agent is done via MSBuild.Sdk.SqlProj Go to your database project directory in parallel to .sqlproj file create a directory like DB.Build under it create DB.Build.csproj copy.pase the content as below

<Project Sdk="MSBuild.Sdk.SqlProj/1.1.0"> <!-- This will pull in the required tools and dependencies to build a .dacpac with .NET Core -->
    <PropertyGroup>
        <TargetFramework>netstandard2.0</TargetFramework>
    </PropertyGroup>
    <ItemGroup>
        <Content Include="..\src\DB\masterdata\**\*.sql" /> <!-- link in the new .csproj to the .sql scripts in your existing database project -->
    </ItemGroup>
</Project>

After run you will see dacpac file appears under DB.Build/bin/Release/netstandard2.0/DB.Build.dacpac

Here's my build agent output (Ubuntu agent on Azure devops)

Starting: SQL DB build Release
==============================================================================
Task         : .NET Core
Description  : Build, test, package, or publish a dotnet application, or run a custom dotnet command
Version      : 2.187.0
Author       : Microsoft Corporation
Help         : https://learn.microsoft.com/azure/devops/pipelines/tasks/build/dotnet-core-cli
==============================================================================
Info: .NET Core SDK/runtime 2.2 and 3.0 are now End of Life(EOL) and have been removed from all hosted agents. If you're using these SDK/runtimes on hosted agents, kindly upgrade to newer versions which are not EOL, or else use UseDotNet task to install the required version.
/opt/hostedtoolcache/dotnet/dotnet build /home/vsts/work/1/s/src/RecommenderAPI.DB/RecommenderAPI.DB/RecommenderAPI.DB.Build/RecommenderAPI.DB.Build.csproj -dl:CentralLogger,"/home/vsts/work/_tasks/DotNetCoreCLI_5541a522-603c-47ad-91fc-a4b1d163081b/2.187.0/dotnet-build-helpers/Microsoft.TeamFoundation.DistributedTask.MSBuild.Logger.dll"*ForwardingLogger,"/home/vsts/work/_tasks/DotNetCoreCLI_5541a522-603c-47ad-91fc-a4b1d163081b/2.187.0/dotnet-build-helpers/Microsoft.TeamFoundation.DistributedTask.MSBuild.Logger.dll" --configuration Release /p:DeployOnBuild=true /p:WebPublishMethod=Package /p:PackageAsSingleFile=true /p:SkipInvalidConfigurations=true /p:PackageLocation=/home/vsts/work/1/recommender-service-cicd/DacPac/
Microsoft (R) Build Engine version 16.5.0+d4cbfca49 for .NET Core
Copyright (C) Microsoft Corporation. All rights reserved.

  Restore completed in 51.72 ms for /home/vsts/work/1/s/src/RecommenderAPI.DB/RecommenderAPI.DB/RecommenderAPI.DB.Build/RecommenderAPI.DB.Build.csproj.
  Using package name RecommenderAPI.DB.Build and version 1.0.0
  Using SQL Server version Sql150
  Deleting existing file /home/vsts/work/1/s/src/RecommenderAPI.DB/RecommenderAPI.DB/RecommenderAPI.DB.Build/obj/Release/netstandard2.0/RecommenderAPI.DB.Build.dacpac
  Writing model to /home/vsts/work/1/s/src/RecommenderAPI.DB/RecommenderAPI.DB/RecommenderAPI.DB.Build/obj/Release/netstandard2.0/RecommenderAPI.DB.Build.dacpac
  RecommenderAPI.DB.Build -> /home/vsts/work/1/s/src/RecommenderAPI.DB/RecommenderAPI.DB/RecommenderAPI.DB.Build/bin/Release/netstandard2.0/RecommenderAPI.DB.Build.dacpac

Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:00:01.71

Finishing: SQL DB build Release

Note: Make sure to restore you NuGet packages in step prior to build

Your ADO pipeline manifest should look like this:

...

- task: DotNetCoreCLI@2
  displayName: 'Restore SQL Project'
  inputs:
    command: 'restore'
    projects: '**/*DB*/*.csproj'
    feedsToUse: 'select'
    vstsFeed: 'db-feed'

...


- task: DotNetCoreCLI@2
  displayName: 'SQL DB build $(buildConfiguration)'
  inputs:
    command: build
    projects: '**/*DB*/*.csproj'
    platform: '$(buildPlatform)'
    arguments: '--configuration $(buildConfiguration) /p:DeployOnBuild=true /p:WebPublishMethod=Package /p:PackageAsSingleFile=true /p:SkipInvalidConfigurations=true /p:PackageLocation=$(Pipeline.Workspace)/$(pipelineArtifactName)/DacPac/'

Alleged

Answer 3

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!

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.

Impaste