Type Conversion Issue in PowerShell Script ( SSAS Tabular Context ) for SQL Server Agent Job

Question

Description:

I am encountering an issue in my PowerShell script, which is intended to be executed as a SQL Server Agent job. The script involves manipulating Microsoft Analysis Services Tabular Model objects. The script works flawlessly when run in a PowerShell editor but fails when executed as a SQL Server Agent job.

Problem:

The specific issue revolves around type conversion errors when trying to set the data source for a partition. The error message is as follows:

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

Code Snippet:

Here's the relevant part of the script:

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("-------------------")
    }
}

Context:

The script is designed for daily processing of MSBI BI database using Analysis Services Tabular Model. The script runs without errors in a PowerShell editor but encounters issues when executed as a SQL Server Agent job.

Request for Assistance:

I'm seeking assistance in understanding why this type conversion error is occurring specifically when the script is run as a SQL Server Agent job. Any insights, suggestions, or alternative approaches to resolve this issue are highly appreciated.

Additional Information:

SQL Server version: 19.1.56.0 PowerShell version: 5.1.17763.4974