How to build and deploy SSAS tabular from VSTS through CI/CD locally and to Azure Analysis services

Question

I am working on a SSAS Tabular project in Visual Studio 2017 where I want to automate build and deploy (and testing) locally and in azure analysis services. The project is connected to an Azure Devops project and it works.

But I am struggling a lot when it comes to build and deploy the project in azure devops. I have followed a blog on this topic (https://notesfromthelifeboat.com/post/analysis-services-1-deployment/) and author has created some powershell scripts that works on my computer when I am running them locally on Windows Powershell ISE. But when I tried to create a build pipeline on devops using the same Powershell file in a Powershell task it fails. I created some variables and set a reference to the powershell file. So far so fine.When I tried to run the build I got an error saying:

Import-Module : The specified module 'SqlServer' was not loaded because no valid module file was found in any module

It seems that powershell in devops are not able to load the Import-Module -Name SqlServer. I have searched on the net for a solution but nothing has worked so far, and other combination of the ImpI have discovered that there is a small difference in the $env:PSModulePath environment variable between in Powershell ISe and in the Powershell ci-build task, but I am not sure if that is the problem.

If any of you have experience knowing how to solve this issue or have a better solution on how to deploy SSAS Tabular model locally and especially deploying to azure (may be some of you have experience with automation) from build/release.

build setup on devops Error from running the build

Powershell script

Command: .\deploy_model.ps1 -workspace c:\develop\tabular-automation -environment validation -analysisServicesUsername test_ssas -analysisServicesPassword test_ssas

  param(
    [Parameter(Mandatory)]
    [string]$workspace,
    [Parameter(Mandatory)]
    [string]$environment,
    [Parameter(Mandatory)]
    [string]$analysisServicesUsername,
    [Parameter(Mandatory)]
    [string]$analysisServicesPassword,
    [string]$databaseServer = "localhost",
    [string]$analysisServicesServer = "localhost"
)


Import-Module -Name SqlServer

$ErrorActionPreference = "Stop"

# Build the model
$msbuild = 'C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\MSBuild\15.0\Bin\MSBuild.exe'
& "$msbuild" TabularExample.smproj "/p:Configuration=$environment" /t:Clean,Build /p:VisualStudioVersion=14.0

# Copy build outputs and deployment options to deployment directory
$deploymentDir = ".\deployment"
mkdir -Force $deploymentDir
cp "bin\$environment\*.*" $deploymentDir
cp .\deploymentoptions\*.* $deploymentDir

# Update deployment targets with parameters
$template = Get-Content .\deploymentoptions\Model.deploymenttargets
$expandedTemplate = $ExecutionContext.InvokeCommand.ExpandString($template)
$expandedTemplate | Set-Content "$deploymentDir\Model.deploymenttargets"

# Create the deployment script
Microsoft.AnalysisServices.Deployment.exe "$deploymentDir\Model.asdatabase" /s:"$deploymentDir\deploy.log" /o:"$deploymentDir\deploy.xmla" | Out-Default

# Deploy the model
$SECURE_PASSWORD = ConvertTo-SecureString $analysisServicesPassword -AsPlainText -Force
$CREDENTIAL = New-Object System.Management.Automation.PSCredential ($analysisServicesUsername, $SECURE_PASSWORD)
Invoke-ASCmd –InputFile "$workspace\$deploymentDir\deploy.xmla" -Server $analysisServicesServer -Credential $CREDENTIAL

You probably need to just install the module first. Take a look at this answer for details. — Matt
Possible duplicate of Azure DevOps Hosted Agents - Add Tool (PS Module) to agent — Matt

Brandon McClure Brandon McClure · Accepted Answer · 2019-02-20T17:17:14

For third party modules, or solution has been to modify the $env:PSModulePath to point to a network location that has the version of the module we want our build agents to run. I use code like below (We also set the PSModulePath to the relative path to our custom modules that are stored in the same repo, but I removed that part of code since you do not state that you have any custom modules)

I like this better than constantly running Install-Module because I have better control over what version of the modules are running, and I do not have to worry about having our build boxes constantly communicating with PowershellGallery

try {
  Import-Module SQLServer -Force -ErrorAction Stop
}
catch {
$networkPath = "\\Network path to Modules\"
    if (!(Test-Path $networkPath)) {
      Write-Error "Can not set env:PSModulePath to the published location on the network" -ErrorAction Stop
    }
    else {
      if (!($env:PSModulePath -like "*;$networkPath*")) {
        $env:PSModulePath = $env:PSModulePath + ";$networkPath"
      }
    }
  }
  else {
    Write-Host "Setting the modulePath to $modulePath"
    if (!($env:PSModulePath -like "*;$modulePath*")) {
      $env:PSModulePath = $env:PSModulePath + ";$modulePath\"
    }
  }

  Import-Module SQLServer -Force -DisableNameChecking -ErrorAction Stop
}

How to build and deploy SSAS tabular from VSTS through CI/CD locally and to Azure Analysis services

1 Answers