In researching some things for a presentation, I was to give at Intersections, Spring 2022, I came across this documentation page for SQL Server PowerShell that in the SQL Server Agent section it indicates that there is a way to get the Agent Job Step to skip loading the SQLPS module and you get to use PowerShell natively and can load whichever modules you would like.
What? Huge News!
How is this done?
According to the documentation page, you simply would put #NOSQLPS in the first line of the SQL Agent PowerShell Job Step as in the image below, and when the job runs, it will NOT load the SQLPS module. This starts in SQL Server 2019, so it has not been back ported to previous versions of SQL Server.
If you want to test this, you can put the #NOSQLPS in the top of the step and then on the second line put ‘Get-Module’ without the quotes and then run the job. When you look at the job output, you will see nothing. If you remove the #NOSQLPS from the job step and put Get-Module in the line, run the job and then look at the output, you will see the SQLPS module be returned.
Hopefully you found this useful.
