If you write code to SQL Server then you might be interested in this: I have written a tSQLt tdd training course which has helped over 500 people learn both tSQLt and how to apply TDD practices to their SQL Server T-SQL development, you can join the course at https://courses.agilesql.club.

Post Deploy Scripts In Composite Dacpacs not deploying

Composite projects in SSDT are a really useful way to logically split databases up while still being able to deploy into a database as a whole. Even if you have a fairly simple database in a single SSDT project you should put your unit tests somewhere. What I like to do is have my solution configured like:

  • Main.dbproj - actual production code and reference data
  • Main.UnitTests.dbproj - unit test code + references to tSQLt etc

The unit tests have a “Same Database” reference pointing to the main project so it is as if my tests were in the same project.

To deploy the unit tests I deploy the unit test project and use option to include composite projects, this then also deploys the production database. When I go to other environments I can deploy the production code without the unit tests. This works really well except when you deploy like this, only the pre/post deploy scripts from the dacpac explicitly being deployed are used. In this case, the scripts in the unit test project are run but not the ones in the main project but when the main project is deployed by itself the scripts are deployed.

To fix this issue what I do is to have an msbuild copy task in the unit test project:




What this means is that the script from the main project is always copied into the test project and so I know that it is always run.

If you do this you need to ensure that people don’t use the script in the test project as the changes are overwritten every build, people don’t normally make that mistake more than once!

It is a little clumsy but a pretty simple solution.

There are cases where this doesn’t work and more complicated things you can do include:

  • Before you deploy, iterate through all the dacpacs and use the .net packaging api to pull out the pre/post deploy scripts then order them as you want and write them to your main dacpac.
  • Write a deployment contributor to do it but there are challenges

I have put a sample on github:

https://github.com/GoEddie/PostDeployCopy

Hopefully it helps someone else.

Ed

Subscribe

* indicates required

Please select all the ways you would like to hear from Agile Sql Club:

You can unsubscribe at any time by clicking the link in the footer of our emails. For information about our privacy practices, please visit our website.

We use Mailchimp as our marketing platform. By clicking below to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing. Learn more about Mailchimp's privacy practices here.