Latest SSDT vs. SqlPackage incompatibility for SQL

问题:

One of our build servers has just been updated to the latest "Microsoft SQL Server Update for database tooling" (version 12.0.60629.0) and is now incompatible with the version of SqlPackage.exe we have (in C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\SqlPackage.exe). The SQL Server database project (targeting SQL Server 2014) builds without error, but when we attempt to deploy the .dacpac to the target server using SqlPackage.exe, we get the following error:

The Element or Annotation class PersistedResolvableAnnotation does 
    not contain the Property class Length.

Sure enough, comparing the model.xml files (inside the .dacpac) for a successful build against this latest one, I see additional "Length" and "Offset" properties under some Annotation elements:

<Annotation Type="PersistedResolvableAnnotation" Name="[$(MyLinkedServer)].[$(MyDatabase)]|[dbo].[SomeTableName]">
    <Property Name="TargetTypeStorage" Value="ISqlSynonymTarget" />
    <Property Name="Length" Value="63" />
    <Property Name="Offset" Value="37" />
</Annotation>

These build servers have only Visual Studio 2012 and 2013, not 2015. It seems fairly clear that this latest SSDT update has broken compatibility with SqlPackage (and there are other indications online of this same issue), but my question is: how do I solve it?

I would rather not try to downgrade to the previous SSDT version but will do so if necessary. Should I install the latest Data-Tier Application Framework and use its SqlPackage.exe instead? Or install Visual Studio 2015?

回答1:

This is an issue introduced in a recent SSDT update. It has been fixed internally and is targeted for inclusion in the next (16.4) release of SSDT. SSDT releases are initially released via the SSDT download page with notifications on the SSDT Team Blog, and then roll out over Visual Studio Extensions and Updates within 2-3 weeks (as this goes on a separate VS-wide schedule).

• Note: For VS2013 releases please go to the "Previous Releases" page on the download site. SSDT continues to be updated for VS2013 but the download page is optimized for users to get the latest version of Visual Studio.

Disclosure: I work on the SSDT team

回答2:

Install the latest DacFx on the build server or change from using the sqlpackage.exe in the msi to the nuget version of the DacFx usnig:

https://blogs.msdn.microsoft.com/ssdt/2016/08/22/releasing-ssdt-with-visual-studio-15-preview-4-and-introducing-ssdt-msbuild-nuget-package/

If this makes dacpac's not backwards compatible then I would raise a connect item for it and see if it a bug, if not it would be good if there was notification of this sort of thing and a connect item should get that rolling.

Ed