How do I programmatically deploy BIDS artifacts to remote SQL Server instance?

Question

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.

Answer 1

Cant help with SSIS but I can help with SSAS and TFS 2010.

A SSAS project wont build with Team Build in 2010. To get a build working will require a msbuild project that calls devenv.exe to do the build and then copy the files to the Team Build output directory.

Here's an example project that I've used before:

    <Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
        <Target Name="Build">
             <PropertyGroup>
                  <DevEnvTool Condition="'$(DevEnvTool)'==''">C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe</DevEnvTool>
                  <DevEnvBuildCommand>"$(DevEnvTool)" "$(MSBuildProjectDirectory)\Nexus_VS2008.sln" /Build</DevEnvBuildCommand>
             </PropertyGroup>
             <Exec Command="$(DevEnvBuildCommand)" />
             <ItemGroup>
                 <SSASSourceFiles Include="$(MSBuildProjectDirectory)\Readify.Nexus.Analysis\bin\Readify.Nexus.Analysis.*"/>
             </ItemGroup>

             <Copy SourceFiles="@(SSASSourceFiles)" DestinationFolder="$(OutDir)" />
        </Target>
    </Project>

This will create the SSAS artifacts in the drop folder of the TFS build. With a little bit of powershell the SSAS cubes can be created and deployed with the help of TFS Deployer. At a minimum the powershell script needs to execute "microsoft.analysisservices.deployment.exe". The script can also be used to changed various configuration settings for SSAS.