I would like to automate the deployment of my SSIS and SSAS artifacts to remote development SQL Server 2005 & 2008 instances on a scheduled basis.
What would be the best solution for this? I am using TFS 2008 as the source control system, so I'd like to integrate the solution with MSBuild and a scheduled Team Build.
SSIS is the easiest, when I was using SSIS, we stored our packages in a file and all we to do was copy the file to the correct directory in C:\Program Files\Microsoft SQL Server\90\DTS\Packages. Which you can do by adding a Copy task to the end of your MSBuild. I am not sure if the xml will be available by default in the output directory, so watch for that.
As for SSAS, I never got around to automating it but, you will want to look into Analysis Management Objects (AMO), pulling from Books online it says:
Analysis Management Objects (AMO) provides a programmatic interface to the complete command set for Analysis Services as available to the developer. Therefore, AMO can be used for deployment and also to the many administrative commands it otherwise supports. For more information about the user of AMO to automate any kind of administrative task, see Analysis Management Objects (AMO).
Good luck