Unable to publish DACPAC to Azure SQL Database using sqlpackage.exe

Question

When trying to apply a DACPAC to an Azure SQL Database I'm getting the following from sqlpackage.exe:

*** An error occurred during deployment plan generation. Deployment cannot continue. The Element or Annotation class SqlDatabaseOptions does not contain the Property class CatalogCollation.

I've managed to use VS2017 with SSDT to create a package and then deploy it to another database using the SQL Server Object Explorer but it fails when I use sqlpackage.exe found in

C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130

The Azure SQL Database Compatibility Level is set to 140 so I'm not sure if SSDT (v 15.6.0) is using a more modern version of sqlpacakage.exe, or where I would find it? I've also tried to run it from the Azure SQL Database Deployment VSTS task and get the same result.

I've tried mapping the command line switches found in this window but again, I still get the same error.

Is there a version of sqlpackage.exe that SSDT is using that I can't find, or is it a parameter that I'm missing?

For reference this is the command that I'm using:

C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130>sqlpackage.exe /SourceFile:"generated.dacpac" /Action:Publish /TargetServerName:"xxxxxxx.database.windows.net" /TargetDatabaseName:"xxxxxxx" /TargetUser:"xxxxxx" /TargetPassword:"xxxxxxx" /TargetTimeout:120

Marashio

Answer 1

It seems you may need to update Microsoft SQL Server Data-Tier Application Framework on your computer. Please download the latest version here. After installation, you will find it on the following path C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\SqlPackage.exe” and updates independently of SSDT / SSMS.

SSMS has its own version of SqlPackage. SSDT has its own version also, which you may find at C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130.

Monumentalize

Thanks, I was missing that. I used the version in C:\Program Files\Microsoft SQL Server\140\DAC\bin – Marashio

Answer 2

Azure SQL is a PaaS Service which means it receives update transparently and relatively often with a new compatibility level.

Azure SQL is now 150.
When you create a new database it will be configured using the latest version. However, your machine, your deployment agents might not have the latest tooling deployed.
When the tooling does not match the compatibility level of your database, the deployment will fail.

If you are deploying using VSTS Hosted Agent you are not in control, and it will always lag a little behind. In that case, the easiest fix is to reduce your database Compatibility level.
This is quite easy as you just need to run an SQL Statement on the database as follow:

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL =  130;

Doctor

Site your sources. This is taken from an article to which you did not give credit – Irresoluble

Since the commenter failed to add the source, this is the one: geeklearning.io/dacpac-and-azure-sql-updates – Exacerbate