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).
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\
C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLASCMDLETS
Type: Command Line
Microsoft.AnalysisServices.Deployment.exe Model.asdatabase /o:Model.xmla /d
Type: Powershell
$erroractionpreference = "stop"
Import-Module -Name %teamcity.build.workingDir%/SQLASCMDLETS
Invoke-ASCmd -InputFile Model.xmla -Server %targetserver%
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.exeMicrosoft.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 processC:\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 serverType: 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}
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