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