Deploying SQL Server dacpac with newest version of SSDT SqlPackage.exe drops column defaults

2
votes

We use Visual Studio database projects and SSDT to version and deploy our SQL Server schema. We compile the project to a dacpac file and then use SqlPackage.exe to publish that dacpac against a SQL Server (2008 R2) instance as necessary.

We recently upgraded from VS 2013 to VS 2015. For a while we were still using a copy of the old SqlPackage.exe (from VS 2013 SSDT) to deploy our dacpacs. However, we just had some new employees start and discovered that, on their machines, deploying a dacpac with the old SqlPackage.exe fails. (VS 2013 has never been installed and DLLs are missing.) I replaced the old SqlPackage.exe (and associated files) with the new SqlPackage.exe (and associated files). The dacpacs now deploy successfully.

The problem: Deploying the dacpac with the new version of SqlPackage.exe does not deploy column defaults (e.g. NEWSEQUENTIALID()). This is clearly unacceptable but reverting to the old version of SqlPackage.exe breaks the deploy for some of our devs. Is this a bug with SSDT or a breaking change that I'm not aware of? Is there some configuration that may be set incorrectly?

2
sql-servervisual-studio-2015sql-server-data-toolsdacpacsqlproj

2 Answers

3
votes

I suggest creating a Microsoft Connect issue under “Developer Tools (SSDT, BIDS, etc.)”

As for how to get the older version of SqlPackage.exe working, note that Visual Studio 2013 shipped with a version of SqlPackage.exe that relies on SQL Server 2012 dependencies. Visual Studio 2015, on the other hand, shipped with a version of SqlPackage.exe that relies on SQL Server 2014 dependencies. I assume that 'the old version of SqlPackage.exe' you're referring to has a version number that starts with 11. If that's correct, your VS2015-only developers would need to install these dependencies for the older SqlPackage.exe:

SqlDom.msi

SQLSysClrTypes.msi

They're available at http://www.microsoft.com/en-us/download/details.aspx?id=39301

1
votes

This appears to be fixed. I tested using version 13.0.3336.1 of sqlpackage.exe from https://www.nuget.org/packages/Microsoft.Data.Tools.Msbuild/ 10.0.60809 by adding a table:

CREATE TABLE myTable (ColumnA uniqueidentifier DEFAULT NEWSEQUENTIALID());

then generating a .dacpac file with /Action:Extract, then dropping the table, then generating a SQL script with /Action:Script. The script contained, in part:

CREATE TABLE [dbo].[myTable] (
    [ColumnA] UNIQUEIDENTIFIER NULL
);

ALTER TABLE [dbo].[myTable]
    ADD DEFAULT (newsequentialid()) FOR [ColumnA];