PowerShell via SQL Agent 2016

Question

PowerShell via SQL Agent 2016

Adam Bean

One Orange Chip

Points: 26631
More actions
April 3, 2017 at 11:37 am

#401889

Hey all,
I can run my PS script just fine via the command line. Yet, when I setup an Agent job for a PowerShell job step and simply call the file "X:\Scripts\File.ps1", it succeeds with no errors; however, the script doesn't appear to fire. What am I missing?
Thanks

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

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 1

OK, If I add a powershell.exe call to it and/or switch to a CmdExec, both now get me something to work off of.

Our scripts rely on modules, and via the Agent, the modules aren't loading:
The specified module 'DBACCollector' was not loaded because no valid module file was found in any module directory. At D:\DBA\Collectors\Collect-Jobs.ps1:10 char:1 + Import-Module DBACCollector -Force -Global + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ResourceUnavailable: (DBACCollector:String) [Import-Module], FileNotFoundException + FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Commands.ImportModuleCommand. Process Exit Code 1. The step failed.

The modules are loaded to their default path %UserProfile%\Documents\WindowsPowerShell\Modules. Executing from a command line, works like a champ. I'm using the same account interactively from the server via command line, as is the calling account (via Proxy) from the Agent.

Any thoughts?

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

Alrighty, so I found and modified the $env:PSModulePath to support a non default path for the modules to live. I moved my modules there, tested via command line, perfect.

Still can't get it to execute properly from an Agent job though. Hrm.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

OK, finally got it. Even though it's a job step of PowerShell, the combo I finally got to work was "powershell.exe -NoProfile -File "X:\File.ps1"

All set

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

Can you mark your own response as the answer?

Gaz

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

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

Thanks.

Gaz

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