PowerShell inside Alteryx Server: can't open an Excel file with $excel.Workbooks.Open

[jdrummey]

Hi,

I'm transitioning a complex ETL process from batch scripts/PowerShell/PowerQuery into Alteryx Server. There's a lot of logic in the existing process so one step in the transition has been to just wrap the existing process in Alteryx's Run Command tool (that kicks off a PowerShell script that runs all the other batch/PS/PQ bits). I've done this and can run the Alteryx workflow perfectly from Alteryx Designer on the Alteryx Server when logged in as the Alteryx Server run-as user. The Run Command tool in the workflow kicks off the first .ps1 script, opens a PowerShell window, runs all the sub-scripts, etc. and completes successfully.

However when I schedule the workflow from Alteryx Server it fails. What's happening is that a number of the sub-scripts all run fine and then when it gets to a bit of PowerShell that is opening MS Excel to run some PowerQuery the PowerShell fails as it's opening the file and I can't figure out why. I've gone through all the Alteryx logs and I can't see anything there, I'm suspecting there's some sort of access control or something issue. Does anyone have any idea why the workflow might run perfectly well in Designer but not Server?

Here's the outer PowerShell script just before the error happens:

& "V:\Scripts\Master\RefreshPQ.ps1" -excelFile "U:\2-Processed Data\Processing\Master Data.xlsx" -queries "Org_Unit:Period"

Now here's the V:\Scripts\Master\RefreshPQ.ps1 script that is being called. The problem happens at line 40 in the $excel.Workbooks.Open($excelFile) command.

# Refresh Power Query queries in an Excel workbook.
# Parameters: name of Excel file, queries to process (one text string with query names separated by :)
# Note: In this script, query names get prefixed with "Query - " before calling the Data Connection to refresh.

param(
    [Parameter(Mandatory=$true)][string]$excelFile, 
    [Parameter(Mandatory=$true)][string]$queries
)

function exitScript($exitCode) {
    $excelInstance = Get-Process "Excel" -ErrorAction SilentlyContinue
    if ($excelInstance) {
        $excelInstance | Stop-Process -Force
        }
    Remove-Variable excelInstance
    [Environment]::Exit($exitCode)
    }

if ($psboundparameters.Count -ne 2) {
[Environment]::Exit(1)
}

Write-Host("RefreshPQ: $excelFile")

$queryNames = $queries -split ":"

try {
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $excel.DisplayAlerts = $false
}
catch {
    [Environment]::Exit(2)
}
finally {}

######################
# This is where the error happens...
######################
try {
    $wb = $excel.Workbooks.Open($excelFile)
}
catch {
    exitScript 3
}
finally {}

foreach ($queryName in $queryNames) {

    try {
        Write-Host("  -$queryName")
        $wb.Connections.Item("Query - " + $queryName).Refresh()
    }
    catch {
        $wb.Close($false)
        exitScript 4
    }
    finally {}

    Start-Sleep 3

 }

try {
    $wb.Save()
}
catch {
    $wb.Close($false)
    exitScript 5
}
finally {}

$wb.Close($false)
exitScript 0

As I'd said this script runs perfectly when run from Alteryx Designer as the same user as Alteryx Server, so there's something extra going on when run from Alteryx Server that I can't figure out. Since the earlier bit of code is launching Excel to generate the $excel object that is apparently working and then it's failing to open the workbook.

Does anyone have any ideas?

Thanks,

Jonathan

[patrick_mcauliffe]

To rule out a network/permissions issue, can you try putting the file directly on the server's C drive and try running it from there?

[jdrummey]

Sorry for not mentioning it in my initial post...I'd already tried that, and gotten the same failure.

[SowmyaNagaraju]

Hi, I'm facing the same issue - while trying to modify Excel file using PowerShell script. Could you please tell if you found the solution to this problem. Thank you!

[TheCoffeeDude]

Login as the user on the server, run Powershell and look at the Set-ExecutionPolicy. More info here: https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.security/set-executionpolicy...

In my environment, I'm not allowed to change the execution policy, so I had to code sign the Powershell script to make it run. Fun times.

[NicolasSz]

Hi @jdrummey , did you find a solution for this ? 

Thanks