Latest SSDT vs. SqlPackage incompatibility for SQL Server 2014

Question

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?

Burns

Answer 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

Decrepitate

Just the man I was hoping to hear from -- thanks Kevin! I'll wait on 16.4 for a fix. Can you tell me if there's a way to downgrade to a version of SSDT that didn't have this problem? Are previous releases made available anywhere? – Burns

@NickJones unfortunately this regression was introduced way back in November. It's possible but you would need an extremely old version - essentially going back to the v120 stack by uninstalling SSDT. I would recommend using the new SqlPackage.exe instead. It's actually installed inside the Visual Studio 12.0 folder - look for SqlPackage.exe and choose the one in the 130 folder. We'll have the fix out in a couple of weeks so hopefully this won't inconvenience you too much. – Decrepitate

The only sqlpackage.exe I see under the Visual Studio 12.0 folder is in C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\sqlpackage.exe; there's no DAC\130 folder. Should I be looking somewhere else? – Burns

If you have the 12.0.60629.0 update (or any update where you can see "SQL Server 2016" in the targets for a DB project) you should see a DAC\130 folder. Is it there? SqlPackage should be included... – Decrepitate

Aha, you're right. I was looking at a machine that hadn't yet gotten the 12.0.60629.0 update. Checking one that has it, I see the DAC\130 folder. I'll adjust our build tools to use that location if available. – Burns

I see there are now 16.4 and 16.5 releases of SSDT. Can you confirm that one of these fixes this regression bug? – Burns

@NickJones yes, this was in 16.4. I'd recommend updating straight to 16.5 though - it's got some nice additional features in SqlPackage – Decrepitate

Answer 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

Maloney

I love seeing SqlPackage.exe in a NuGet package but it's a project-level package, which doesn't suit my usage. I expect to have solution-wide build/test tools like this in solution-level packages (which were removed in v3 and are now being added back). So I'm stuck with DacFx, it seems. – Burns

If you download nuget.exe you can control where it is extracted to (so you could reference that in your deploy scripts across all projects) using: "\nuget.exe install Microsoft.Data.Tools.Msbuild -ExcludeVersion -Verbosity detailed -Source nuget.org/api/v2" it should be easier to maintain that than the msi – Maloney

@EdElliott just a quick note - if you're targeting SQL2014 or earlier we do our best to be backwards compatible with older versions of the tools. In this case it was a regression where we serialized data that wasn't necessary & that caused the min DacFx version to be bumped. We've fixed this, but of course would prefer if you used the latest & greatest tools – Decrepitate