PowerShell script failing when run from SQL Agent

Question

I have created a PowerShell script that does the following:

1. Copies a template Excel file to another directory
2. Imports data into this template file using the ImportExcel module created by Doug Finke ( https://www.powershellgallery.com/packages/ImportExcel/7.1.0 )
3. Runs a macro that is contained in the template file
4. Renames the file as an .xlsx file type

When I run this script from the server, it executes perfectly and does what is expected. But when I tried to set up a SQL Agent job to run this same PS script, it is failing and I cannot find the reason it would do so.

Here is my PS script:

# Directory Information
$RootDirectory = "D:\MSSQL\SSIS\BIN Commission Report\"
$ArchiveFolder = "Archive\"
$SourceFolder = "Source\"
$TemplateFolder = "Template\"

# File Information
$TemplateFile = "Commission_Invoices_Template.xlsm" #Template file with macro
$MacroFile = "Commission_Invoices.xlsm" #Macro-enabled file with vendor data
$VendorFile = "Commission_Invoices.xlsx"  #File provided by the vendor
$DataImportFile = "Commission_Invoices_Import.xlsx" #File used to import data into SQL
$AppliedMacroFile = "Commission_Invoices_Import.xlsm"

#Full paths for each file
$TemplatePath = $RootDirectory, $TemplateFolder, $TemplateFile -Join ""
$MacroPath = $RootDirectory, $SourceFolder, $MacroFile -Join ""
$VendorPath = $RootDirectory, $SourceFolder, $VendorFile -Join ""
$DataImportPath = $RootDirectory, $SourceFolder, $DataImportFile -Join ""
$AppliedMacroPath = $RootDirectory, $SourceFolder, $AppliedMacroFile -Join ""

If ( Test-Path $MacroPath ) { Remove-Item $MacroPath }
If ( Test-Path $AppliedMacroPath ) { Remove-Item $AppliedMacroPath }

#Copy template file to use for date insertion and macro
Copy-Item $TemplatePath -Destination $MacroPath

# Copy data from vendor spreadsheet into macro-enabled workbook
Import-Excel -Path $VendorPath -WorksheetName Sheet1 | Export-Excel $MacroPath -WorksheetName Commission_Invoice

# Run the macro in Excel
$MacroName = "CommissionPrep"

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$WB = $Excel.Workbooks.Add($MacroPath)
$Excel.Run($MacroName)

#Close Excel
$WB.Close($false)
$Excel.Quit()

[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($WB)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)

Remove-Variable -Name Excel

# Copy data to xlsx file from xlsm for data import into SQL
Import-Excel -Path $AppliedMacroPath -WorksheetName Commission_Invoice | Export-Excel $DataImportPath -WorksheetName Commission_Invoice -AutoSize

If ( Test-Path $MacroPath ) { Remove-Item $MacroPath }
If ( Test-Path $VendorPath ) { Remove-Item $VendorPath }
If ( Test-Path $AppliedMacroPath ) { Remove-Item $AppliedMacroPath }

This is what my SQL Agent job looks like:

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -File "D:\MSSQL\SSIS\BIN Commission Report\Scripts\CommissionInvoices_PS.ps1"

And here is the error message when run as a SQL Agent job:

Message
Executed as user: CompanyX\CompanyXSQLADMIN. 
Microsoft Excel cannot access the file 'D:\MSSQL\SSIS\BIN Commission Report\Source\Commission_Invoices.xlsm'. 
There are several possible reasons:  
 The file name or path does not exist.  
 The file is being used by another program.  
 The workbook you are trying to save has the same name as a currently open workbook.  

At D:\MSSQL\SSIS\BIN Commission Report\Scripts\CommissionInvoices_PS.ps1:35 char:1  + $WB = $Excel.Workbooks.Add($MacroPath)  + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : OperationStopped: (:) [], COMException + FullyQualifiedErrorId : System.Runtime.InteropServices.COMExceptionException calling "Run" with "1" argument(s): "Cannot run the macro 'CommissionPrep'. The macro may not be available in this workbook or all macros may be disabled."  

At D:\MSSQL\SSIS\BIN Commission Report\Scripts\CommissionInvoices_PS.ps1:36 char:1 + $Excel.Run($MacroName) + ~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : COMException

You cannot call a method on a null-valued expression.

At D:\MSSQL\SSIS\BIN Commission Report\Scripts\CommissionInvoices_PS.ps1:39 char:1 + $WB.Close($false)  + ~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNullException calling "ReleaseComObject" with "1" argument(s): "Object reference not set to an instance of an object."

At D:\MSSQL\SSIS\BIN Commission Report\Scripts\CommissionInvoices_PS.ps1:45 char:1 + [System.Runtime.Interopservices.Marshal]::ReleaseComObject($WB) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : NullReferenceException 0 'D:\MSSQL\SSIS\BIN Commission Report\Source\Commission_Invoices_Import.xlsm' file not found  

At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.0.1\Public\Import-Excel.ps1:105 char:17 + throw "'$($Path)' file not found" + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : OperationStopped: ('D:\MSSQL\SSIS\... file not found:String) [], RuntimeException + FullyQualifiedErrorId : 'D:\MSSQL\SSIS\BIN Commission Report\Source\Commission_Invoices_Import.xlsm' file not found. Process Exit Code 1.  The step failed.

I am unclear as to how this script executes perfectly when run from the PowerShell ISE shell when run locally on the server but fails it is run via a SQL Agent job. Any suggestions on how to fix this?

Answer 1

have you tried to set the executionpolicy for your SQL Agent job:

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -executionpolicy bypass -File "D:\MSSQL\SSIS\BIN Commission Report\Scripts\CommissionInvoices_PS.ps1"