如何通过CI/CD在本地从VSTS生成SSAS表格并将其部署到Azure分析服务 [英] How to build and deploy SSAS tabular from VSTS through CI/CD locally and to Azure Analysis services

问题描述

我正在Visual Studio 2017中从事SSAS表格项目，我想在本地和天蓝色分析服务中自动化构建和部署(和测试).该项目已连接到Azure Devops项目，并且可以正常工作.

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.

但是在以天蓝色的开发人员构建和部署项目时，我非常费劲.我关注了有关此主题的博客( https://notesfromthelifeboat.com/post/(analysis-services-1-deployment/)和作者创建了一些Powershell脚本，当我在Windows Powershell ISE上本地运行它们时，它们可以在我的计算机上运行.但是，当我尝试在Powershell任务中使用相同的Powershell文件在devops上创建构建管道时，它失败了.我创建了一些变量，并设置了对powershell文件的引用.到目前为止一切顺利.当我尝试运行构建时，出现错误消息:

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:

导入模块:未加载指定的模块'SqlServer' 因为在任何模块中都找不到有效的模块文件

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

似乎devops中的powershell无法加载Import-Module -Name SqlServer.我在网上搜索了一个解决方案，但到目前为止没有任何效果，ImpI的其他组合发现在Powershell ISe和Powershell ci-build任务之间$ env:PSModulePath环境变量之间存在细微的差异，但我不确定是否是问题所在.

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.

如果您中有任何人知道如何解决此问题，或者对如何在本地部署SSAS表格模型，尤其是从构建/发行版到Azure部署(可能有些人具有自动化经验)有更好的解决方案，

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.

在devops上进行构建设置 运行版本出错

Powershell脚本

Powershell script

命令:.\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

推荐答案

对于第三方模块，或者解决方案是修改$env:PSModulePath使其指向具有我们想要构建代理的模块版本的网络位置跑步.我使用如下代码(我们还将PSModulePath设置为存储在同一存储库中的自定义模块的相对路径，但由于您未声明有任何自定义模块，因此我删除了这部分代码)

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)

与持续运行Install-Module相比，我更喜欢这样做，因为我可以更好地控制正在运行的模块的版本，并且不必担心构建框与PowershellGallery不断进行通信

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
}

这篇关于如何通过CI/CD在本地从VSTS生成SSAS表格并将其部署到Azure分析服务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！