By Richie Lee

Hello!

Recently someone got in touch with me about one of my earliest posts. I realised I had made a mistake on that post and had updated. But I also realised that the script itself is actually part of a PowerShell module. So wanting to make things as easy as possible, and being the helpful guy that I am, I have altered that example into a script which I am posting here.

It’s worth stressing that the errors that output to the console are fairly generic, and that there is a log file that will contain all the information if there are any errors.


function publish-SsasDb
{
[CmdletBinding()]
param(
[Parameter(Position=0,mandatory=$true)]
[string] $TargetSsasSvr,
[Parameter(Position=1,mandatory=$true)]
[string] $sourceDb,
[Parameter()]
[switch] $runscript,
[Parameter()]
[switch] $createScript,
[Parameter()]
[switch] $deployScript)

$script:ASDeployWizard = "C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment.exe"


if(!(Test-Path $sourceDb))
{
Write-Error -message "The path to [$sourceDb] is either not accessable or does not exist" -category InvalidArgument
return
}

Write-Host "$(Get-Date): Starting"

Trap {Write-Host "Failed to publish database ($_.Exception.Message)" -foregroundcolor Black -backgroundcolor Red; break}

$path = [System.IO.Path]::GetDirectoryName($sourceDb)
$targetDatabase = [System.IO.Path]::GetFileNameWithoutExtension($sourceDb)
$outputScript = [System.IO.Path]::ChangeExtension($sourceDb,"xmla")
$deploymenttargets = [System.IO.Path]::ChangeExtension($sourceDb,"deploymenttargets")
$logPath = [System.IO.Path]::ChangeExtension($sourceDb,"log")

if($runscript -or $createScript)
{
$arguments = @("`"$sourceDb`"", "/s:`"$logPath`"", "/o:`"$outputScript`"")
}
if($deployScript)
{
$arguments = @("`"$sourceDb`"", "/s:`"$logPath`"")
$arguments
}
Write-Verbose "$(Get-Date): Start deployment wizard from deployment script located at [$sourceDb]. See log file [$logPath] for more info."
Start-Process -FilePath $script:ASDeployWizard -ArgumentList $arguments -Wait
Write-Verbose "$(Get-Date): End deployment wizard from deployment script located at [$sourceDb]. See log file [$logPath] for more info."

if($runscript -or $createScript)
{
if(!(Test-Path $outputScript))
{
Write-Error -message "Path [$outputScript] does not exist. Deployment script has not been generated as expected." -category InvalidOperation
return
}
}
$xmla = [string]::join([Environment]::NewLine, (Get-Content $outputScript))
if($runscript)
{
$xmla = [string]::join([Environment]::NewLine, (Get-Content $outputScript))
Write-Verbose "$(Get-Date): Start Database deployment script [$outputScript]"
$xmlaresult = Execute-Xmla $TargetSsasSvr $xmla
Write-Verbose "Xmla Result: $xmlaresult"
Write-Verbose "$(Get-Date): Finished Database deployment script [$outputScript]"
}
Write-Host "$(Get-Date): Finished"
}

function Execute-Xmla
{
[CmdletBinding()]
param(
[Parameter(Position=0,mandatory=$true)]
[string] $TargetSsasSvr,
[Parameter(Position=1,mandatory=$true)]
[string] $xmla
)
begin
{
Add-Type -AssemblyName "Microsoft.AnalysisServices.AdomdClient, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$connectionectionString = "Data Source=$TargetSsasSvr;Provider=MSOLAP.4;Integrated Security=SSPI;Impersonation Level=Impersonate;"
$connection = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdConnection($connectionectionString)
}
process
{
$connection.Open()
$comand = $connection.CreateCommand()
$comand.CommandTimeout = 20000
$comand.CommandType = [System.Data.CommandType]::Text
$comand.CommandText = $xmla
$reader = $comand.ExecuteXmlReader()
if($reader.Read())
{
$return = $reader.ReadOuterXml()
}
return $return
}
end
{
$connection.Close();
$connection.Dispose();
}
}