January 28, 2012 
A few tips to get you started:
Community TFS Build Extensions can be download here from CodePlex. This is a collection of activities you can integrate into your process template.
I used this blog posting from Ewald Hofman to configure my Visual Studio 2010 environment to edit the process template. I add the process template as “link” to the template project so it remains in the appropriate folder.
I do that so I don’t have the dreaded “one or more children have validation errors or warnings” on every activity in the process template.
Here is the walkthrough that is posted on MSDN to accomplish the task, and here are the issues I have with that particular walkthrough:
- Hardcoded name of a deploymanifest for the database project you wish to deploy
- Only supports a single database project
- Process is executed on the Controller and not the Agent
I made the following changes to the build process which better suited my requirements:
- Scan the BinariesDirectory for all .deploymanifest files
- Execute VSDBCMD for each .deploymanifest in the BinariesDirectory
- Execute the VSDBCMD process on the Agent not the Controller
Here is how my the Deploy Database Projects looks in the designer in Visual Studio 2010
If the build runs successfully you should get something similar to the following output written to the Log of your build.
A few things to note:
Make sure the account configured to run the Agent has the appropriate permissions in SQL Server or you may get errors similar to the following:
.Net SqlClient Data Provider: Msg 262, Level 14, State 1, Line 1 CREATE DATABASE permission denied in database ‘master’.
If you are unsure as to where the database project is deploying just have a look at the deploymanifest and check the contents of TargetConnectionString.
You can easily verify the account that is running the Team Build Service (Agent) by launching the Team Foundation Server Administration Console and click Build Configuration the account running the service is displayed to the right.
You can download the Process Template I used for this blog post here. If you have any questions feel free to post a comment.
Azure DevOps and Visual Studio Awesomeness 
Hi,
This is a helpful article. Thanks for posting.
I am new and try to create some sort of template in VS2010 to deploy the DB stuff but unsuccessful. I start over and used the templates from your post and it didn’t get to execute the VSDBCMD call. Do you know of any reason that stops with no error?
Thanks for your help.
LikeLike
Hi,
I am not sure what you mean by “stops with no error”. I am aware of a bug in the build template where if you have spaces in your version control path the Invoke VSDBCMD process fails. Luckily the fix is easy, you can download a new copy of the build process from the link in my blog posting or you can edit the Arguments value on the Invoke VSDBCMD activity yourself.
You can find the activity to edit at: Deploy Database Projects | If | Sequence | ForEach | Invoke VSDBCMD
Please change the Arguments value to:
String.Format(“/a:Deploy /dd+ /dsp:Sql /manifest:””{0}”””, deployManifest)
LikeLike
Thanks for your help. It works.
LikeLike