I'm setting up database projects for the first time and I'm trying to build/deploy using Azure DevOps. I'm using the MSBuild task hosted in Azure (windows-2019) for the build. I'm using a Command Line task running on the SQL server to execute SQLPackage.exe from a working directory of
C:\Program Files (x86)\Microsoft Visual Studio\2019\BuildTools\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140
.
I'm getting errors for the master & msdb databases during deployment with SqlPackage.exe:
No file was supplied for reference master.dacpac; deployment might fail. When C:\Jen_DacpacTest\Artifact\whatever.dacpac was created, the original referenced file was located C:\PROGRAM FILES (X86)\MICROSOFT VISUAL STUDIO\2019\ENTERPRISE\COMMON7\IDE\EXTENSIONS\MICROSOFT\SQLDB\EXTENSIONS\SQLSERVER\140\SQLSCHEMAS\MASTER.DACPAC.
I thought that the references to master and msdb were resolved on build and again on deploy (since it's a variable for the path) but that doesn't seem to be the case. It seems to figure out what $(DacPacRootPath) is during the build and "hardcodes" the references in the model.xml file generated into the dacpac. The references look like this in the proj file:
<ArtifactReference Include="$(DacPacRootPath)\Extensions\Microsoft\SQLDB\Extensions\SqlServer\140\SqlSchemas\master.dacpac">
<HintPath>$(DacPacRootPath)\Extensions\Microsoft\SQLDB\Extensions\SqlServer\140\SqlSchemas\master.dacpac</HintPath>
<SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
<DatabaseVariableLiteralValue>master</DatabaseVariableLiteralValue>
</ArtifactReference>
<ArtifactReference Include="$(DacPacRootPath)\Extensions\Microsoft\SQLDB\Extensions\SqlServer\140\SqlSchemas\msdb.dacpac">
<HintPath>$(DacPacRootPath)\Extensions\Microsoft\SQLDB\Extensions\SqlServer\140\SqlSchemas\msdb.dacpac</HintPath>
<SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
<DatabaseVariableLiteralValue>msdb</DatabaseVariableLiteralValue>
</ArtifactReference>
Here's what it looks like in the model.xml file if the dacpac is unpacked:
<CustomData Category="Reference" Type="SqlSchema">
<Metadata Name="FileName" Value="C:\PROGRAM FILES (X86)\MICROSOFT VISUAL STUDIO\2019\ENTERPRISE\COMMON7\IDE\EXTENSIONS\MICROSOFT\SQLDB\EXTENSIONS\SQLSERVER\140\SQLSCHEMAS\MASTER.DACPAC" />
<Metadata Name="LogicalName" Value="master.dacpac" />
<Metadata Name="ExternalParts" Value="[master]" />
<Metadata Name="SuppressMissingDependenciesErrors" Value="False" />
</CustomData>
<CustomData Category="Reference" Type="SqlSchema">
<Metadata Name="FileName" Value="C:\PROGRAM FILES (X86)\MICROSOFT VISUAL STUDIO\2019\ENTERPRISE\COMMON7\IDE\EXTENSIONS\MICROSOFT\SQLDB\EXTENSIONS\SQLSERVER\140\SQLSCHEMAS\MSDB.DACPAC" />
<Metadata Name="LogicalName" Value="msdb.dacpac" />
<Metadata Name="ExternalParts" Value="[msdb]" />
<Metadata Name="SuppressMissingDependenciesErrors" Value="False" />
</CustomData>
So I'm wondering how this is supposed to work since Azure MSBuild task (and Visual Studio build task) which are hosted in Azure will reference the master and msdb dacpacs from a Visual Studio Enterprise folder but on the SQL Server which is where SqlPackage.exe is run to do the deployment those files are found in the BuildTools folder instead of Enterprise. (And I don't really think I should have to install VS Enterprise edition on my SQL server to get this to work?)
In Azure:
C:\PROGRAM FILES (X86)\MICROSOFT VISUAL STUDIO\2019\\**ENTERPRISE**\\COMMON7\IDE\EXTENSIONS\MICROSOFT\SQLDB\EXTENSIONS\SQLSERVER\140\SQLSCHEMAS
On the SQL Server:
C:\Program Files (x86)\Microsoft Visual Studio\2019\\**BuildTools**\\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\140\SQLSchemas
It seems weird that the path to these files on the build server would have to exactly match what's on the sql server. I thought there would be a bit more magic happening to find these dacpac references since from what I understand you aren't supposed to manually add them to your project and drag them around everywhere.
Database projects deployment - No file was supplied for reference master.dacpac
This is a typical SSDT issue. The path to these files on the build server is not the main cause of your issue.
Please check No file was supplied for reference ABC.dacpac; deployment might fail and Error: The reference to external elements from the source named 'master.dacpac' could not be resolved.
Try copying the master.dacpac
to the same folder where your xx.dacpac
exists. (You can do this by using copy/xcopy
command in cmd) And make sure the working directory of your CMD task is in same folder. Hope it helps :)