SQL Agent PowerShell Syntax Error

If you are running this as a SQL Server Agent job step you will need to use the Operating system (CmdExec) step type, not a PowerShell type.

Within the CmdExec it would be best to create a file on the machine with all of the steps that you require and then call that.

For example create a file C:\PowerShellScripts\MyScript.ps1 which contains...

Import-Module e:\dbatools-master\dbatools-master\dbatools.psd1
Copy-SqlLogin -Source ServerA -Destination ServerB

And then call that script using powershell.exe "& 'C:\PowerShellScripts\MyScript.ps1'" in the CmdExec job step.

To expand the reason you can't generally use the PowerShell step with another module is the SQL Server provider. It does not play nice and there is a connect item out to get MS to fix this.

If you add $erroractionpreference = 'stop' to the your job step you will see the full output of why the command is erring:

Executed as user: NT Service\SQLSERVERAGENT. A job step received an error at line 92 in a PowerShell script. The corresponding line is 'if (Get-Command TabExpansionPlusPlus\Register-ArgumentCompleter -ErrorAction Ignore) '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'SQL Server PowerShell provider error: Path SQLSERVER:\TabExpansionPlusPlus does not exist. Please specify a valid path. '. Process Exit Code -1. The step failed.

The provider is the issue in most cases as it does not know how to interpret some characters/commands. If the PowerShell step type put you within the context of the PowerShell.exe host, our life would be so much easier and awesome things could happen.