TFS build with DB, SSAS, and SSRS
Read a good kickstart article on how to use TFS to build/deploy the VS Database Projects (http://www.mssqltips.com/sqlservertip/3584/introduction-to-team-foundation-server-tfs-build-for-business-intelligence-bi-projects).
My prior experience is that schema-related changes are the most troublesome to deploy when dealing with an existing database, especially when there is a chance of destroying data (renaming+recasting a column). These one-off issues can be handled by manually scripting. The manual scripts still need to be orchestrated (run in a particular order, verify only run once, deal with a script failure). However, for stateless SQL code (stored procedures, functions, etc.), the deployment of TFS works well.
Play around with the .DBPROJ publish option and you’ll find ways to exclude tables (along with other objects you may not want to promote) in your deployment pattern. Definitely potential there.
One of the comments mentioned using an MSBUILD file to build/deploy SSAS (and SSRS) artifacts as well. I haven’t tried that but can definitely see the value in making that a part of my overall build solution.
