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.
Firstly I always recomend seperating the build and deployment processes. They are not one and the same, and seperation makes it easier to use more mature configuration management workflows. An example of this would be being able to promote a previous build (that's been through a systest environment and given the all clear) to a UAT environment as a release candidate. You don't want to rebuild to perform this. Capture your build artefacts, and deploy from them seperately.
Anyway...
SSIS is fairly straightforward : all the build does really is copy packages into the output folder, so you just need to grab them somehow (depends what you use to build with - I use TeamCity). Then in your deployment process, you can fairly easily use SMO to upload them to the SSIS server:
$app = new-object Microsoft.SqlServer.Dts.Runtime.Application
$app.SaveToSqlServerAs($packageObj, $null, "\\$folderName\$($packageObj.Name)$packageNameSuffix", $serverName, $null, $null);
(Interestingly this doesn't seem to go via the SSIS service itself, but via the MSDB stored proc API. Not that it makes much difference either way, but it works even when the user doesn't have access to the SSIS service remotely because of that DCOM issue)
SSAS is a lot harder. Whilst you can do a heap of stuff with AMO, which I do as part of my deployments, I've never found an easy way of taking the outputs from the solution build (like the .asdatabase file) and converting them into the XMLA required to create the olap database schema from scratch. There's a transformation missing, which I couldn't be bothered to write.
Instead I use the SSAS deployment utility, which can be used from the command line as part of a deployment process, get it to generate the XMLA and then execute it:
$asDeploy = "$programfiles32\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Microsoft.AnalysisServices.Deployment.exe"
write-host "Generating XMLA"
start-process -wait -FilePath:$asDeploy -ArgumentList:"$pwd\..\bin\MyOlap\MyOlap.asdatabase","/d","/o:$pwd\MyOlap.xmla"
if (-not $?){
throw "Failed to generate XMLA: errors were reported above";
}
write-host "Deploying SSAS Database"
.\ascmd.exe -S $olapServer -i "$pwd\MyOlap.xmla"
if (-not $?){
throw "Failed to deploy cube: errors were reported above";
}
I make sure the deployment above doesn't process the cube (I re-write the .deploymentoptions file) so that I can subsiquently use AMO to run though the deployed cube, and updating datasources as required for that environment. Then I kick off a process.
You didn't ask, but for SSRS you can just grab the RDLs from the build, and use the webservice API to deploy them, and obviously for databases you'd use a SQL GDR project, and use the command line deployment tool there. Taken together you can deploy an entire BI project from a single script with fairly tight control around versioning etc...
I've used these approaches for the last 5 years or so on various projects, and acrued a library of really useful PowerShells for doing this. One day I will clean these up and release them.
[17/May] Note: the SQL 2008 R2 version of the deployment wizard (in the SQL/100/ folder) is marked as a GUI app, not a console app (like 2005 was), so the script as previously written doesn't wait for it to complete! Code above changed to use start-process with explicit '-wait' instead. This is a nasty gotcha.
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).
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.
I found lots of posts advocating scripting VS to automate the build, which isn't always possible in a CI environment.
With a bit more digging I also found http://sqlsrvanalysissrvcs.codeplex.com/ which includes an msbuild task for creating .asdatabase files in msbuild.
