Monday, 17 November 2014

SQL Server Database Projects and Visual Studio 2012, 2013 - Why Doesn't My DACPAC Post-Deployment Script Run?

SQL Server Database Projects are a new feature from Visual Studio 2012 onwards.
The SQL DACPAC format is generated by SQL Database Projects and is a Microsoft-endorsed way for deploying SQL schema changes as part of a deployment package (also known as a Data Tier Application). These can be deployed by a setup package or even a SQL Database Administrator through SQL Server Management Studio.

I noticed today that every time I built my project, it would include the wrong post-deployment SQL script file inside my DACPAC - so my reference tables were not populated with data correctly.

Turns out that there can only ever be one designated Post-Deployment Script in a SQL Server Database Project. I originally thought it was just a naming convention that was used to find the designated post-build file (i.e. anything named "Script.PostDeployment*.*".

This was not correct - you just have to make sure it is labelled as the Post-Deployment script for that project as per the file properties below.

As a side note, I generated the SQL MERGE statements for reference/lookup data using the dbo.sp_generate_merge tool on github: