In Visual Studio your database project is published without error to your environment. But somehow it fails in your deployment pipeline where it is published using sqlpackage.exe. Would it not be great if you could get some detailed logging of what exactly is going wrong?
Add the /Diagnostics parameter to your publish command to get get more logging then you can handle 😉 Example below:
Write-Host "Deploying configuration database"
& sqlpackage.exe /action:publish /Diagnostics:True /SourceFile:"$($ConfigurationDatabaseDacPacLocation)" /Profile:"$($ConfigurationDatabasePublishXMLLocation)"In this case I found that my build was incorrectly overwriting the publish.xml files belonging to the different database projects. This caused all projects to deploy into the same database. This is typically a problem that will not popup when you publish your project from Visual Studio.
The full CLI reference for SQLPackage.exe can be found here: https://learn.microsoft.com/en-us/sql/tools/sqlpackage/cli-reference?view=sql-server-ver16