Hosted VS2017 agent build master.dacpac does not exist

Question

My solution created with VS2017 Professional contains an SQL Server Database Project that references the master database. When using the Hosted VS2017 agent to build my solution in Visual Studio Team Services I'm getting the errors below:

2017-07-14T12:44:17.8387743Z ##[error]C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\MSBuild\Microsoft\VisualStudio\v15.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets(559,5): Error SQL72027: File "C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\master.dacpac" does not exist. 2017-07-14T12:44:17.8397816Z C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\MSBuild\Microsoft\VisualStudio\v15.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets(559,5): Build error SQL72027: File "C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\master.dacpac" does not exist. [d:\a\3\s\Main\ItAsset.Database\ItAsset.Database.sqlproj]

How can I fix this and get my solution to build in VSTS?

Sedgewake

This bug has been fixed in Visual Studio 2019 and Visual Studio 2017 version 15.9.13. See here - developercommunity.visualstudio.com/content/problem/124214/… – Aronow

I have the same problem although the path is not absolute anymore. – Pejorative

Answer 1

I just got bit by this in a multi-developer situation. It seems to happen in VS2017 SSDT projects where the developer who checked in the code originally had their installation of Visual Studio in a different path than you, or another instance of Visual Studio. For example if developer A installed to defaults on C:\ but developer B installed his VS2017 to E:\ drive, whoever creates the reference to Master will work, the other will not find the dacpac file.

Looking in the .sqlproj file, you'll likely find this reference to the Master database:

 <ArtifactReference Include="C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\130\SqlSchemas\master.dacpac">
  <HintPath>$(DacPacRootPath)\Extensions\Microsoft\SQLDB\Extensions\SqlServer\130\SqlSchemas\master.dacpac</HintPath>

Note: the <HintPath> is correct, but the Include=" is a hard coded path. It seems that the hint path is not followed as it normally should be. To fix your problem, try copying the contents of the HintPath element to the Include attribute. Leave the HintPath as it is.

<ArtifactReference Include="$(DacPacRootPath)\Extensions\Microsoft\SQLDB\Extensions\SqlServer\130\SqlSchemas\master.dacpac">

Narrate

Thank you so much! This fixed my problem! – Lubricant

This bug has been fixed in Visual Studio 2019 and Visual Studio 2017 version 15.9.13. See here - developercommunity.visualstudio.com/content/problem/124214/… – Aronow

Thanks! This fixed some old code I edited in VS2019 and tried to build with a hosted agent – Fluidextract

Answer 2

This is a bug in SSDT for Visual Studio 2017.

The workaround is to manually edit the project file(s) replacing the full path with the $(DacPacRootPath) variable. Or, you can use SSDT for Visual Studio 2015.

https://feedback.azure.com/forums/908035-sql-server/suggestions/32897047-visual-studio-2017-ssdt-adds-hardcoded-master-dacp#comments

8/12/2019 Update - This bug has been fixed in Visual Studio 2019 and Visual Studio 2017 version 15.9.13. See here - https://developercommunity.visualstudio.com/content/problem/124214/visual-studio-2017-ssdt-adds-hardcoded-mmsdb-andor.html

Aronow

Rather than copying around files, I agree this is broken and needs fixing. – Narrate

Thanks for the additional details @LarrySmith – Aronow

Anytime my friend. This one was a real mystery and totally frustrating in my team almost as soon as we upgraded to 2017. Accusations were cast and victims were hunted. ;) Your post put us on the right path, literally. – Narrate

Answer 3

It uses the absolute path that isn’t existing in Hosted VS2017 agent. (Professional vs Enterprise). You can check project file (open sqlproj file via nodepad)

You can copy master.dacpac to your project folder and include it to project, then add the reference to this file.

Helenahelene

use @LarrySmith's answer instead. You dont have to include dacpacs in your project. – Misfire

Can you please remove this answer? This is not the way you should solve the problem created by the bug. Larry Smith's answer as the best answer until the bug is fixed. – Aronow

@JasonKohlhoff Sorry, it throws "you cannot delete this accepted answer". – Helenahelene

Answer 4

We had development machines with different versions of Visual Studio. I used a condition to specify the HintPath

  <Choose>
      <When Condition="Exists('C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\130\SqlSchemas\master.dacpac')">
          <ItemGroup>
            <ArtifactReference Include="C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\130\SqlSchemas\master.dacpac">
              <HintPath>C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\130\SqlSchemas\master.dacpac</HintPath>
              <SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
              <DatabaseVariableLiteralValue>master</DatabaseVariableLiteralValue>
            </ArtifactReference>
            <ArtifactReference Include="C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\130\SqlSchemas\msdb.dacpac">
              <HintPath>C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\130\SqlSchemas\msdb.dacpac</HintPath>
              <SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
              <DatabaseVariableLiteralValue>msdb</DatabaseVariableLiteralValue>
            </ArtifactReference>
          </ItemGroup>
      </When>
      <Otherwise>
          <ItemGroup>
            <ArtifactReference Include="C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\130\SqlSchemas\master.dacpac">
              <HintPath>C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\130\SqlSchemas\master.dacpac</HintPath>
              <SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
              <DatabaseVariableLiteralValue>master</DatabaseVariableLiteralValue>
            </ArtifactReference>
            <ArtifactReference Include="C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\130\SqlSchemas\msdb.dacpac">
              <HintPath>C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\130\SqlSchemas\msdb.dacpac</HintPath>
              <SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
              <DatabaseVariableLiteralValue>msdb</DatabaseVariableLiteralValue>
            </ArtifactReference>
          </ItemGroup>
      </Otherwise>
  </Choose>

Neely

Answer 5

Trying to open an SSDT in VS2019 that was created in VS2017, requires change

From:

<ArtifactReference Include="C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\130\SqlSchemas\master.dacpac">

To:

<ArtifactReference Include="C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\130\SQLSchemas\master.dacpac">

NOTE: Enterprise Visual Studio

Waldgrave

Please see the answer by Larry Smith above. – Aronow