2

I currently have a two step SQL job that's intention is to do the following;

1) Check file exists. (issue lies here)

2) Run SSIS package to import a csv file to a database.

$File = "\\File\File2\Text.csv"

if(!(Test-Path -Path $File))
{
Write-Error "File does not exist"
}

I get the error below, despite the job compleating succsefully.

"The job script encountered the following errors. These errors did not stop the script: A job step received an error at line 0 in a PowerShell script. The corresponding line is ''. Correct the script and reschedule the job. The error information returned by PowerShell is: 'File does not exist '. Process Exit Code 0. The step succeeded."

The full intention is for then job to fail intentionally, then to quit reporting success.

Popular search methods have returned mutiple variants of the code provided, yet none seem to interact with SQL jobs as I expect. Any ideas?

Thanks in advance!

user2261755
  • 159
  • 1
  • 3
  • 17
  • 2
    Try `$ErrorActionPreference="Stop"` at the beginning of your script, and/or using `throw` rather than `Write-Error`. You can also test what happens if you just do `exit 1` (to return a non-zero exit code, if that's what it's testing). – Jeroen Mostert Jun 20 '19 at 14:24
  • The command of 'throw' returns an error as follows; A job step received an error at line 3 in a PowerShell script. The corresponding line is 'throw "Error trying to do a task" '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Error trying to do a task '. Process Exit Code -1. The step failed. – user2261755 Jun 20 '19 at 14:30
  • Alright, so -- that's what you want? In the sense that it makes the step fail. What you literally ask for, "for the job to fail intentionally, then to quit reporting success" is of course a contradiction in terms -- either the job reports failure or success, it cannot fail as a whole while reporting success. It can have a failing *step* while still reporting success, though. – Jeroen Mostert Jun 20 '19 at 14:35
  • The difficulty is that the powershell script will fail regardless of file existing or not. There's a requirment for the job to run every 15 minutes, but only import the file if it exists. – user2261755 Jun 20 '19 at 14:44
  • Ah, then your issue may simply be that the script cannot, actually, see the file (so as far as PowerShell cares it's right). Is the path a share? Does the user the script run under have access to that share? By default, a PowerShell step will run under the account of SQL Server Agent, and that will be a local account with restricted permissions (and specifically no access to network shares). You can create a custom credential to run the PowerShell script under if that's the issue. (If you do `dir $file` instead of `Test-Path`, it should fail with an explicit and possibly insightful error.) – Jeroen Mostert Jun 20 '19 at 14:49
  • The location is a share, being open access to all. SQL Agent runs under a proxy domain account. – user2261755 Jun 20 '19 at 15:06

0 Answers0