Build error in tfsbuild with database project

Question

I have setup my .NET project in TFS Build. It builds fine locally, but I am getting this build error on the server:

The imported project "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.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.

I have no idea what it is, even googling didn't help much. My solution has some database projects. What is the issue here and how I can fix it?

Hose

Answer 1

You need to install the SQL Server Data Tools on all build agents. When installing a TFS Build server, people often create agents to be on that or other machines.

You can download them from the MS SQL Server Data Tools Blog or Microsoft SQL Server Data Tools, and you choose the one that corresponds to the builds that you doing (Visual Studio 2010 or Visual Studio 2012).

Note: In some cases, the error mentions the path:
c:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlTasks.targets
In such cases, install SQL Server Data Tools from SQL installation, not the link above.

Labaw

This needs to be installed on the build server? Where can I download it? – Hose

Sorry my bad, - Ssetting up TFS Build agents. See here : msdn.microsoft.com/en-us/library/bb399135.aspx – Labaw

Answer 2

I have solved a similar issue by adding a new Solution Configuration that does not build the SQLPROJ. I then configured CI-CD (Azure DevOps Pipelines) to build that configuration. In a subsequent step of the Build, I exclusively build and pack the SQLPROJ. By thisI avoid having two Solutions - one of which will only have on project; the SQL Project.

Balderas

Answer 3

If this problem occurs on Azure DevOps pipeline build agent, then please use MSBuild@1 instead of DotNetCoreCLI@2 in the pipeline YML task. It worked for me.

- task: MSBuild@1
  displayName: 'Build SQL solution'
  inputs:
    solution: <SQL DB Solution>.sln
    msbuildArchitecture: x64
    msbuildArguments: '/property:DSP="Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider"'
    configuration: 'release'

Mandola

Answer 4

For quite a while already we use ReadyRoll.MSBuild NuGet package. Event that ReadyRoll is not ReadyRoll anymore, it is still being regularly updated and keeps working with most recent SCA addins. It contains same build extensions, and works flawlessly on our build agents without need to install anything. Biggest problem is absence of nuget support in .sqlproj files, which makes adding/updating packages bit tricky: https://documentation.red-gate.com/rr1/installing/build-components

SQL Change Automation documentation doesn't contain this section, so it's hard to say for sure how long will it stay the way it is.

For the sake of completeness, properties to add to your .sqlproj as of today:

<ReadyRollNuGetBaseFolder>$(MSBuildThisFileDirectory)..\packages</ReadyRollNuGetBaseFolder>
<ReadyRollNuGetIsRestored Condition="$([System.IO.Directory]::GetDirectories($(ReadyRollNuGetBaseFolder), 'ReadyRoll.MSBuild.*').Length) != 0">True</ReadyRollNuGetIsRestored>
<SqlChangeAutomationTargetsPath Condition="$(ReadyRollNuGetIsRestored) == 'True'">$([System.IO.Directory]::GetDirectories($(ReadyRollNuGetBaseFolder), 'ReadyRoll.MSBuild.*')[0])\tools\ReadyRoll.Data.Schema.SSDT.targets</SqlChangeAutomationTargetsPath>

Neron