Possible to compile sql change scripts from VCS?

I'm working on a CI and Auto Deploy configuration using TeamCity. The projects are .NET, using SVN for source control and SQL Server.

I'd like to know if and how it is posible to auto deploy sql scripts within my solution.

Our solution structure typically looks like this:

 > MySolution.Data
    > 3.0 Scripts
       * 01 - UpdateUserTable.sql
       * 02 - UpdateRoleTable.sql
    > 3.1 Scripts
       * 01 - CreateJobTable.sql
       * 02 - InsertJobTypes.sql
       * 03 - AndSoOn.sql

I am still relatively new to TeamCity, so I'm not sure which way I should go about achieving my goal. My current line of thinking is as follows:

1) Get TeamCity trigger a build when a *.sql file is added to the solution. I stress added as I want this to be the rule that distiguishes between using the .sql file or not. (Is this what I would use artifacts for?)

2) Having triggered the build, compile the added .sql files into a single file and... go to step 3


2) For each *.sql file added, ordered alphabetically... go to step 3

3) Execute the sql file(s) on the sql server. Not sure how to do this yet. Perhaps using something like this:

msdeploy.exe -verb:sync -source:dbFullSql="?:\???\deploymentScript.sql" -dest:dbFullSql="Data Source=.;Initial Catalog=DbDeploymentTestApp;User ID=sa;Password=password" 

Any chance this has already been done by an existing tool? Or has someone had experience doing something similar? Would love to know if what I am looking at doing is possible and if so how it should be done.

I've posted a similar question on StackOverflow, but thought I'd go directly to the horse's mouse. http://stackoverflow.com/questions/18589906/autodeploy-sql-changes-with-teamcity-alternative-to-redgate?noredirect=1#comment27363087_18589906

Please sign in to leave a comment.