在Azure DevOps上构建sqlproj

Question

我正在尝试使用Azure Core从GitHub构建我的.NET DevOps 2.1解决方案。它包含一个TargetFrameworkVersion为v4.6.2的SQL项目。这个项目总是无法构建。

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)

如何为构建服务器引用或包含这些目标？它在VS2017中构建得很好。我花了一天多的时间寻找，但找不到关于这个问题的任何信息。

Answer 1

感谢Herman Cordes指导我的调查。

问题出在选定的生成服务器上。SSDT是一个仅适用于Windows的软件包，因此我不得不使用Windows而不是默认的Ubuntu，并且使用DotNetCoreCLI@2.而不是VSBuild@1任务

azure-pipelines.yml

pool:
  vmImage: 'vs2017-win2016'

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

编辑：MSBuild@1任务也可以使用。

Answer 2

在Azure DevOps CI/CD管道上构建SQL Server项目时，我也遇到了完全相同的问题。任何预构建的构建任务都不能为我工作！

我通过避免在解决方案中添加SQL Server项目解决了这个问题。

我通过使用MSBuild SDK实现了这一点，它能够从SQL脚本集生成SQL Server数据层应用程序包(.dacpac)。通过将第二个项目添加到解决方案中，我成功地继续利用了通过Visual Studio上的SQL Server对象资源管理器将该项目链接到实时数据库的优势。我在这个answer中对我的实现进行了更详细的解释。

Answer 3

这里是一个linux构建代理的解决方案在linux代理上构建DacPac文件最简单的方法是通过MSBuild.Sdk.SqlProj在数据库项目目录中创建.sqlproj文件在该目录下创建一个类似DB.Build的目录创建DB.Build.csproj copy.pase内容如下

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

运行后，您将看到dacpac文件出现在DB.Build/bin/Release/netstandard2.0/DB.Build.dacpac下

这是我的构建代理输出( Azure devops上的Ubuntu代理)

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://docs.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

注意:在构建之前，请确保在步骤中恢复NuGet包

您的ADO管道清单应如下所示：

...

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