I have written a few posts on the upcoming Visual Studio Team System for Database Professionals product (more succinctly known as Data Dude) and of late I have been using the product more and more. Its still in beta and as such there are a few bugs still to be ironed out but there are some really cool features in it and one of them that I want to talk about herein is post-deployment scripts.
VSTS4DBP gives us the ability automate database builds using MSBuild.exe and this means that your database builds can be integrated into your Continuous Integration builds which is really powerful stuff. The core build features are all around the creation of schema objects and I'm not going to talk too much about that (yet) but you also have the ability to execute scripts pre and post the deployment of the schema objects. The post-deployment scripts are where you can deploy your static data. The term "static data" in this context refers to all the data that needs to be in the database from the moment it goes live.
Here is the default post-deployment script (called Script.PostDeployment.sql) along with what you see in Solution Explorer:
There's three important things to note here:
The Build Action property of Script.PostDeployment.sql is set to 'PostDeploy'. This is what makes this particular script the post deployment script, the name of it inconsequential. The Build Action property is defined by an enumeration consisting of 'PostDeploy', 'Not In Build', 'PreDeploy' & 'Build'.
The solution contains, by default, three extra scripts called Permissions.sql, RulesAndDefaults.sql, Signatures.sql. You'll have to take my word for it that the Build Action for these three scripts is 'Not In Build' which means they are only included in the build if they are called from elsewhere
These three scripts are indeed called from elsewhere, namely from the Script.PostDeployment.sql script. Note the use of "SQLCMD :r" which will cause the three scripts to be loaded into the final built script.
The three scripts Permissions.sql, RulesAndDefaults.sql, Signatures.sql are, by default, empty although you can go in and change them to your heart's content. What is really compelling though is the ability to call your own scripts and it should be evident by now how easy it is to do this. Simply add them to your solution in the Post-Deployment folder and edit Script.PostDeployment.sql accordingly.
In the next screenshot I show a script that I have added to my solution and the edited Script.PostDeployment.sql
That's really all you have to to include scripts to populate your tables into your database project. When you build the project the output is a single .sql file that will create all of the objects in the solution and automatically include the contents of "INSERT MyTable.sql" which can of course be whatever you want it to be.
I hope that's given you a quick intro to of one of the cool new features in VSTS4DBP. There are lots lots more and I'll endeavour to cover some of them over the coming weeks.
-Jamie