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:
https://github.com/readyroll/generate-sql-merge

DDK

1 comment:

Anugraha Jain said...

Hello David,

We have developed a new gen developer friendly BI framework with some extremely unique features. Would like to give you early access & love to hear your opinion. Please do let me know of how to reach out to you. Would be launching product in 1week from now.

Also could you please share your email details for further communication.

Regards,
Anugraha