Deploy VS 2010 database project using MSBuild and TeamCity

Question

Is it possible to deploy a VS 2010 database project using TeamCity?

I am building my whole solution, and deploying a website to my server, this all works fine.

The final step I want to trigger is the deploy of the database project which generates a sql script and deploys it.

I have the "Create a deployment script (.sql) and deploy to the database" option selected as a deploy action, my Configuration target is set to build and deploy the database project, but I just can't figure how to get TeamCity and MSBuild to trigger it.

Answer 1

Visual Studio must be installed for this to work.

For the original SQL Server 2005/2008 Database Project types:

1. Create a build step of runner type Visual Studio to build the solution.
2. Create a build step of runner type Command Line. Set Command Executable to C:\Program Files\Microsoft Visual Studio 10.0\VSTSDB\Deploy\VSDBCMD.exe. Set Command Parameters to /a:Deploy /dd:+ /manifest:%system.teamcity.build.checkoutDir%\<PROJECT PATH>\sql\debug\<PROJECT NAME>.deploymanifest. (See here for VSDBCMD.exe parameters).

For the SQL Server Database Project provided by SQL Server Data Tools or Visual Studio 2012/2013:

1. Create a build step of runner type Visual Studio to build the solution.
2. Create a build step of runner type Command Line. Set Command Executable to C:\Program Files\Microsoft Visual Studio 10.0\Microsoft SQL Server Data Tools\sqlpackage.exe. See here for sqlpackage.exe parameters. Here's an example with the deployment settings stored in an XML file (created via the Publish option): /Action:Publish /SourceFile:%system.teamcity.build.checkoutDir%\<PROJECT PATH>\bin\Debug\<PROJECT NAME>.dacpac /Profile:%system.teamcity.build.checkoutDir%\<PATH TO PROJECT>\PublishSettings.xml.