PowerShell脚本在SQL Server Agent作业中的SSAS表格模型类型转换问题

描述 #

我编写的用于操作Microsoft Analysis Services（SSAS）表格模型对象的PowerShell脚本，在PowerShell编辑器中运行正常，但作为SQL Server Agent作业执行时出现问题。

问题 #

设置分区数据源时触发类型转换错误，错误信息如下：

Cannot convert the "Microsoft.AnalysisServices.Tabular.ProviderDataSource" value of type "Microsoft.AnalysisServices.Tabular.ProviderDataSource" to type "Microsoft.AnalysisServices.Tabular.ProviderDataSource".

代码片段 #

相关函数代码如下：

Function  CreatePartition(  $partitonPrefix,   $partitonSuffix,  $query,  $datasource,   $reprocess)
{
    $spartition = $partitonPrefix + $partitonSuffix #concatinate to make partition name string
    Write-Output(" ")
    If ($mg.Partitions.Contains($spartition)) #check if partition exists
    {
        Write-Output("-------------------")
        Write-Output("Partition Exists: " + $spartition)
        if ($reprocess -eq "D")
        {
            Write-Output("Process Full on: " + $spartition)
            $date1 = Get-Date
            $partition = $mg.Partitions[$spartition]
 
            $partition.RequestRefresh([Microsoft.AnalysisServices.Tabular.RefreshType]::Full);
            # Save changes
            $db.Model.SaveChanges();
             $date2 = Get-Date
             Write-Output("Create Partition Completed: " + $spartition)
             Write-Output("Duration: " + ($date2 - $date1).ToString())
             Write-Output("-------------------")
        }
        else
        {
            Write-Output("Nothing done")
        }
        Write-Output("-------------------")
    }
    else #create the partition
    {
        Write-Output("-------------------")
        Write-Output("Partition not found: " + $spartition)
        Write-Output("Creating partition: " + $spartition)
        $date1 = Get-Date
        $sourceQuery = $query
 
        # Retrieve the data source using the data source name
        #$dataSource = $db.Model.DataSources | Where-Object { $_.Name -eq $datasourceName }
 

      # Create a new partition object for the specific table
        $partition = New-Object Microsoft.AnalysisServices.Tabular.Partition
        $partition.Name = $spartition
        $partition.Source = New-Object Microsoft.AnalysisServices.Tabular.QueryPartitionSource
        $partition.Source.Query = $sourceQuery
        [Microsoft.AnalysisServices.Tabular.ProviderDataSource]$partition.Source.DataSource = [Microsoft.AnalysisServices.Tabular.ProviderDataSource]$db.Model.DataSources[$datasourceName]
        #$partition.Source.SetSource($dataSource)
 
        $partition.Mode = "Import"
 
        # Getting the partition info
        Write-Output("DataSource Type: " + $db.Model.DataSources[$datasourceName].GetType().FullName)
        Write-Output("DataSource Type: " + $partition.Source.DataSource.GetType().FullName)
      # Add the partition to the table's partitions collection
        $mg.Partitions.Add($partition)
 
      # Process the partition
      Write-Output("Process Full on: " + $spartition)
        $partition.RequestRefresh([Microsoft.AnalysisServices.Tabular.RefreshType]::Full);
      # Save changes
        $db.Model.SaveChanges()
 

        $date2 = Get-Date
        Write-Output("Create Partition Completed: " + $spartition)
        Write-Output("Duration: " + ($date2 - $date1).ToString())
        Write-Output("-------------------")
    }
}

上下文 #

该脚本用于每日处理基于SSAS表格模型的MSBI数据库，仅在SQL Server Agent作业环境下报错。

请求协助 #

希望了解为何仅在SQL Server Agent作业中出现该类型转换错误，寻求排查思路或解决方法。

补充信息 #

• SQL Server版本：19.1.56.0
• PowerShell版本：5.1.17763.4974

排查思路与解决方法 #

1. 程序集版本冲突
   这种同类型无法转换的错误，核心原因是SQL Server Agent和本地PowerShell加载了不同版本的Microsoft.AnalysisServices.Tabular程序集。SQL Server Agent默认使用SQL Server自带的程序集，而本地编辑器可能使用客户端工具或单独安装的AS SDK版本。

• 解决：在脚本开头明确指定加载对应版本的程序集，示例：

Add-Type -Path "C:\Program Files\Microsoft SQL Server\160\SDK\Assemblies\Microsoft.AnalysisServices.Tabular.dll"

根据你的SQL Server版本调整路径（SQL Server 2022对应160目录），强制统一加载版本。

2. 执行环境权限与路径差异
   SQL Server Agent的服务账户可能没有程序集目录的读取权限，或者无法自动找到程序集路径（本地PowerShell用的是当前用户环境，路径和权限更宽松）。

• 解决：
  • 在SQL Server Agent作业的PowerShell步骤中，指定完整的程序集加载路径；
  • 给SQL Server Agent服务账户分配程序集所在目录的读取权限；
  • 检查作业的执行策略，确保允许加载外部程序集。

3. 移除冗余的类型转换代码
   代码中强制转换的写法属于冗余操作，反而可能触发类型加载异常。直接替换为：

$partition.Source.DataSource = $db.Model.DataSources[$datasourceName]

让PowerShell自动处理类型匹配。

4. 优化数据源获取方式
   改用命名空间的内置方法获取数据源，避免类型匹配问题：

$dataSource = $db.Model.DataSources.GetByName($datasourceName)
$partition.Source.DataSource = $dataSource

5. 添加环境调试日志
   在脚本中加入程序集版本打印，对比本地和Agent环境的差异：

$asm = [System.Reflection.Assembly]::GetAssembly([Microsoft.AnalysisServices.Tabular.ProviderDataSource])
Write-Output "Loaded Assembly Version: $($asm.FullName)"

内容的提问来源于stack exchange，提问作者wou