SQL Server Agent Job is not invoking Powershell Script

Question

I have Powershell Script that converts XLS files into CSV file(s). it is working fine if I run this script manually but it is not working with SQL Server Agent Job. SQL Server Agent job is not throwing any error but also script is not doing anything as it is not converting XLS file into CSV.

Code of Powershell Script (Convert2CSV.ps1)

$Dir="C:\Source\TEST\Deployment\ReportOutput"
$Files =Get-Childitem $Dir -filter "*.xls"
foreach ($file in $Files)
{
    $excelFile = "$Dir\"+$File

    $Excel = New-Object -ComObject Excel.Application
    $wb = $Excel.Workbooks.Open($excelFile)

    foreach ($ws in $wb.Worksheets) 
    {
        $ws.SaveAs($excelFile.Replace(".xls","") + ".csv", 6)
    }
    $Excel.Quit()
}

SQL Server Agent Job Step

Below is the Powershell ExecutionPolicy on the machine. Sql Server and Powershell Script is on the same machine

Martin Cairney Martin Cairney · Accepted Answer · 2021-01-28T06:11:59

There is no error handling in the PowerShell script so you will struggle to fully debug it anyway.

As it is failing from SQL Agent then I suspect it is down the the Service Account and permissions.

SQL Server Agent Job is not invoking Powershell Script

1 Answers