I have a problem with the processing of a SQL 2016 tabular model via a SSIS-Script task.
In the SSIS script task I invoke a powershell script the following way:
RunspaceConfiguration runspaceConfiguration = RunspaceConfiguration.Create();
Runspace runspace = RunspaceFactory.CreateRunspace(runspaceConfiguration);
runspace.Open();
// Configure Variables
Pipeline pipeline = runspace.CreatePipeline();
string query = Dts.Variables["XMLA"].Value.ToString();
//Read in powershell script
string script = readFile(@"folder");
script = script.Replace("plchldr_query", query);
pipeline.Commands.AddScript(script);
writeFile(@"folder", script);
try
{
pipeline.Invoke();
} catch(Exception e)
{
writeFile(@"folder", e.Message);
}
This powershell script only consists of two lines (plus some lines for debugging):
Import-Module SQLASCMDLETS -Verbose 4>&1 | Out-File "folder\filename.txt"
Invoke-ASCmd -Query "plchldr_query" -Server "Servername\TABULAR"
Unfortunately when I start the SSIS package the Invoke-ASCmd fails with the error message:
Could not load file or assembly 'Microsoft.AnalysisServices.PowerShell.Cmdlets' or one of its dependencies. Invalid Pointer (Exception from HRESULT: 0x80004003 (E_POINTER))
When I start exactly the same script directly in powershell everything works fine.
For me it looks like the problem could be connected with different hosts. When I start the script directly in powershell the host is "Console Host" (Version 4.0). But when I start the script via SSIS-Task the host is "Default Host" (Version 4.0).
To check which modules are available I did execute the command:
Get-Module -ListAvailable | out-file "folder\file"
I thought that this list might be different, but it doesn´t matter wether I execute this command directly in powershell or via SSIS, the result is the same:
ModuleType Name ExportedCommands
---------- ---- ----------------
Manifest SqlServer {Invoke-ProcessCube, New-SqlCngColumnMas...
Manifest BitsTransfer {Complete-BitsTransfer, Add-BitsFile, Re...
Manifest CimCmdlets {New-CimSessionOption, Get-CimAssociated...
Script ISE {Import-IseSnippet, Get-IseSnippet, New-...
Manifest Microsoft.PowerShell.Diagnostics {New-WinEvent, Export-Counter, Get-WinEv...
Manifest Microsoft.PowerShell.Host {Start-Transcript, Stop-Transcript}
Manifest Microsoft.PowerShell.Management {Remove-WmiObject, Remove-EventLog, Add-...
Manifest Microsoft.PowerShell.Security {Get-Credential, Get-Acl, Set-Authentico...
Manifest Microsoft.PowerShell.Utility
Manifest Microsoft.WSMan.Management {Test-WSMan, Set-WSManInstance, Get-WSMa...
Manifest PSDiagnostics {Set-LogProperties, Enable-WSManTrace, S...
Binary PSScheduledJob {Get-JobTrigger, Add-JobTrigger, Get-Sch...
Manifest TroubleshootingPack {Get-TroubleshootingPack, Invoke-Trouble...
Manifest AppvClient
Binary ThirdPartyNotice
Binary UEV {Restore-UevUserSetting, Get-UevAppxPack...
Manifest SQLASCMDLETS {Add-RoleMember, New-RestoreFolder, Invo...
Manifest SQLPS {Backup-ASDatabase, Get-SqlInstance, New...
So, now I´m totally stuck. For me the only difference between the two types of executions is the host in which the script is executed. But both hosts have the same modules available. Do you maybe have another idea what could cause this different behaviour?
Thanks in advance!
Update:
I now checked the Import-Module SQLASCMDLETS
call via -Verbose
. When I execute this command in the powershell I get the following output:
Importing cmdlet 'Add-RoleMember'.
Importing cmdlet 'Backup-ASDatabase'.
Importing cmdlet 'Invoke-ASCmd'.
Importing cmdlet 'Invoke-ProcessASDatabase'.
Importing cmdlet 'Invoke-ProcessCube'.
Importing cmdlet 'Invoke-ProcessDimension'.
Importing cmdlet 'Invoke-ProcessPartition'.
Importing cmdlet 'Invoke-ProcessTable'.
Importing cmdlet 'Merge-Partition'.
Importing cmdlet 'New-RestoreFolder'.
Importing cmdlet 'New-RestoreLocation'.
Importing cmdlet 'Remove-RoleMember'.
Importing cmdlet 'Restore-ASDatabase'.
The output for execution with SSIS is:
Loading module from path 'C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLASCMDLETS\SQLASCMDLETS.psd1'. Loading module from path 'C:\Program Files (x86)\Microsoft SQL Server\130\Tools\ PowerShell\Modules\SQLASCMDLETS\Microsoft.AnalysisServices.PowerShell.Cmdlets.dl l'.
I checked that the files are present in the folders. But the messages seem for me like that the loading of the modules happens differently. Could that be possible? If yes, does anybody knows why? Or do I misinterpret the different outputs?
User contributions licensed under CC BY-SA 3.0