Invoke-ASCmd works when executed directly in powershell but not when started via SSIS Script Task

1

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?

sql-server
powershell
ssis
ssas
cmdlets
asked on Stack Overflow May 9, 2017 by Torben • edited May 11, 2017 by Torben

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0