Trying to Execute a SQL Agent Job With Powershell in Job Steps

Question

Trying to Execute a SQL Agent Job With Powershell in Job Steps

Polymorphist

SSCommitted

Points: 1909
More actions
November 11, 2014 at 3:56 pm

#292378

Hello -
I'm really stuck on this issue i'm having today. Here's my set-up:
1) I created a SQL Agent job which calls an .exe file to connect to a web service and retrieve data back to a .csv file. I'm using Powershell in the job step to run the .exe file.
2) Once the .csv file lands in the folder, another job step runs which calls a stored procedure to bulk insert the data from the .csv file into a table in SQL Server.
The issue i'm having is that the job runs just fine when executing the SQL job manually. However, once I schedule the job it errors-out saying that step 2 cannot complete because the .csv file does not exist. I'm sort of figuring out that this is a permissions issue somewhere but since i'm new to Powershell i'm not sure exactly what that is.
Has anyone encountered this issue? I'm in a big time-crunch and have to get this out tomorrow, what can I do to make this happen?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gary Varga SSC Guru Points: 82166 More actions · Answer 1

Your best bet at this stage, especially with what little detail you have provided, is to get ProcMon (or whatever it is called nowadays). This, in case you don't know, is a tool developed by SysInternals amd maintained since Microsoft bought SysInternals.

Using ProcMon you are extremely likely to find out what the issue is.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

Polymorphist (11/11/2014)
Hello -
I'm really stuck on this issue i'm having today. Here's my set-up:
1) I created a SQL Agent job which calls an .exe file to connect to a web service and retrieve data back to a .csv file. I'm using Powershell in the job step to run the .exe file.
2) Once the .csv file lands in the folder, another job step runs which calls a stored procedure to bulk insert the data from the .csv file into a table in SQL Server.
The issue i'm having is that the job runs just fine when executing the SQL job manually. However, once I schedule the job it errors-out saying that step 2 cannot complete because the .csv file does not exist. I'm sort of figuring out that this is a permissions issue somewhere but since i'm new to Powershell i'm not sure exactly what that is.
Has anyone encountered this issue? I'm in a big time-crunch and have to get this out tomorrow, what can I do to make this happen?

It's not PowerShell. The login that SQL Server uses needs to have privs to where the file is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ManicStar SSCoach Points: 17992 More actions · Answer 3

Jeff Moden (11/12/2014
It's not PowerShell. The login that SQL Server uses needs to have privs to where the file is.

Yep, try running your individual job steps as the user that the job is running under. That should flush out the underlying problem.

Gazareth One Orange Chip Points: 27737 More actions · Answer 4

If it's not permissions, could the job be jumping onto the next step before the .exe has finished, i.e. before the csv file has been created?

Not sure at what point PS would report finished if it's calling another executable.