Using custom cmdlet from SQL Server

Question

I want to execute a Powershell script from SQL Server's maintenance plan. This is fine and perfectly possible, but what if I want to use a custom cmdlet? Can this still work from the Powershell script SQL Server job step (in this case, I need to use the SCVMM cmdlet).

Answer 1

No this won't work from SQL Agent PowerShell job step because SQL Agent uses sqlps, the SQL Server minishell. Since the minishell does not support adding cmdlets either via add-pssnapin or import-module, there is no way to add the SCVMM cmdlets.

Instead use a CmdExec (Operating System) job step and specify regular PowerShell. For example (not sure of commands to add SCVMM cmdlets)

C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.EXE -command "add-pssnapin SCVMM;invoke-someCmd"

or put the commands in a script file and call the script:

C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.EXE -file"C:\Scripts\Invoke-SCVMM.ps1"