I have a database project in Visual Studio. Inside the database, I use a couple of system views to fetch some metadata about tables. To make the project build successfully, you need to add a reference to the master database in the project.
I created a build pipeline in Azure Devops, which builds the Visual Studio solution (which creates the dacpac of the database), then copies the dacpac and a publish profile to the artifact staging folder, publishes those and finally deploys the database (using the dacpac and the publish profile) to the dev server. This looks like this (yes I use the classic editor):
It all works without a problem. I then created a release pipeline to deploy the same dacpac of my data warehouse to another environment (this can be QA or production). I essentially used the same last step as in the build pipeline – the Azure SQL DB deployment task – but instead of using the artifact staging directory, I used the published resources. This time however, the deployment did not succeed. I was greeted with the error “[error]Error SQL0: The reference to external elements from the source named ‘master.dacpac’ could not be resolved, because no such source is loaded.“.
The error makes sense: I have a reference to a database called master in my project, so sqlpackage.exe (the process that does the actual deployment) is going to look for a dacpac called master.dacpac. But it isn’t present in the published resources of the build pipeline. When you build the project in Visual Studio, this dacpac is created as well.
The resolve this issue, you simply have to copy this dacpac to the artifact staging directory as well.
You don’t have to do anything special with it, it just needs to be there when sqlpackage.exe wants to deploy your database dacpac. The only thing bothering me is that my build pipeline was succeeding even though I did not copy the master dacpac to the artifact staging directory. Why wasn’t it complaining about a missing master.dacpac over there? If you have any idea, let me know in the comments.
------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂
