Getting Analysis Serivce Deployment Wizard running in TeamCity

This process is tested with a tabular model on ssas 2014 and that you have the appropriate licences to use the software.
First, ensure SSAS Deployment Wizard works on your local machine by installing appropriate prerequisits ssms and stuff (not listed here).

Add deployment wizard to repo

Microsoft.AnalysisServices.Deployment.exe
Microsoft.AnalysisServices.Deployment.exe.config
Microsoft.AnalysisServices.DeploymentEngine.dll (exists in GAC only)
Microsoft.AnalysisServices.DLL
Microsoft.DataWarehouse.DLL
Microsoft.DataWarehouse.Interfaces.DLL
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Dmf.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoExtended.dll
Microsoft.SqlServer.SqlEnum.dll
Microsoft.SqlServer.SqlWmiManagement.dll
Microsoft.SqlServer.WizardFramework.dll (exists in GAC only)

Possible locations for these are
C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies
C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies
C:\Windows\assembly\GAC_MSIL\

Add powershell module to repo

Needed by powershell deploy and process
C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLASCMDLETS

Create a build configuration ("Build") 

Create one msbuild step building the dbproj with default settings, this step will output .asdatabase-file (among others)

Create build configuration ("Deploy")

This will work as deploy build for all your environments (test, acc...)

Create %targetserver% in Build Parameters

Choose Edit and select Prompt to have the possiblity to select your destination environment for deploy

Use artifact dependency on "Build" to extract the .asdatabase file and (optionally) extract environment specific .configsettings-files from your environment config structure (example): 
MyTabular/bin/Model.asdatabase => 
MyTabular/bin/%TargetServer%/Model.configsettings => 

Ensure dll path and powershell modules are specified in version control / checkout rules.

Create three steps in "Deploy" configuration

Ssas deployment wizard

Will output .xmla file used for deploying.
Type: Command Line
Microsoft.AnalysisServices.Deployment.exe Model.asdatabase /o:Model.xmla /d

Powershell deploy

Deploys generated .xmla file to selected server
Type: Powershell
$erroractionpreference = "stop"
Import-Module -Name %teamcity.build.workingDir%/SQLASCMDLETS
Invoke-ASCmd -InputFile Model.xmla -Server %targetserver%

(Optional) Process Cube

Processes cube. Invoke-AsCmd will not throw error so we check for exception manually and throw
Type: Powershell
$erroractionpreference = "stop"
Import-Module -Name %teamcity.build.workingDir%/SQLASCMDLETS
$result = Invoke-AsCmd -query "<Process xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine""> <Type>ProcessFull</Type> <Object> <DatabaseID>BoughtTabular</DatabaseID> </Object></Process>" -Server %Environment%
if ($result.Contains("<Exception ")){throw $result}

Comments

Popular posts from this blog

SSIS: Set parent package variable from child package

How to decrypt stored password from SSMS registered servers

Insert bulk statement does not support recompile (SQL 2017)