How to Create Useful DACPAC Versioning with SSDT Deployment

deploymentsql serverssdtversion control

It took me almost one day to go through lots of articles and blogs to realize check-in driven continuous integration with SQL Server Database Projects (SSDT) using TFS and msbuild. Now once this is working properly, I would like to introduce versioning.

The database project's dacpac properties allow us to fill in a version in the format "x.x.x.x" that is getting published with the dacpac to Sql Server and can be queried there using:

select * from msdb.dbo.sysdac_instances_internal

This is nice but I wonder how to create something more useful and practical than a manually editable text for the version number? Of course the version number will never change as long it's up to developers to adjust it in the database project's properties…

Remembering how assembly version for example in c# project works, there is some magic increment possible by defining something like

"1.2.."

including the build number. How can this be done?

Best Answer

We finally solved this by adding the build numbers in short and long format to

a) The target database as user-defined extended properties

b) A versioning table

Related Solutions

Sql-server – Deployment script always re-create constraints and indexes

It is normally because there is a difference between how they are written and how sql stores them.

As a one time thing to a compare back from the db to the project and you should see some difference on the keys, apply them and it should stop doing it.

SQL Server – SSDT Create User in a Database Not Being Part of the Project

The action described in the question should not be a problem. I was able to create a Post Deployment script and add the following code:

USE [tempdb];
GO
PRINT DB_NAME();

IF (USER_ID(N'g') IS NULL)
BEGIN
    PRINT 'Creating user [g]...';
    CREATE USER [g] WITHOUT LOGIN;
END;
ELSE
BEGIN
    PRINT 'Dropping user [g]...';
    DROP USER [g];
END;

It builds and publishes with no errors, and executes the commands as expected.

Make sure that your SQL script is truly a Post Deployment script. If it is set to Build Script then it probably will get those errors. Look at the properties of the script and make sure that Build Action is set to PostDeploy.

And please make sure that you are using the latest update of SSDT. The most recent version can be found here:

Download Latest SQL Server Data Tools

Best Answer

Related Solutions

Sql-server – Deployment script always re-create constraints and indexes

SQL Server – SSDT Create User in a Database Not Being Part of the Project

Related Question