Define a custom workflow to Deploy Database Project(s) from Team Build 2010

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.