Skip to main content

Task Process - PowerShell​

The PowerShell Task lets you execute PowerShell code on the fly or an existing PowerShell file.

Credentials

To control a remote computer you may need to use a Credential. The Credential must match the user name and password of the user that you want to login for. Select a Credential in the combo box or click the Settings icon to open Manage credentials in order to add or edit Credentials.

Scope

You can execute on the local machine or on a remote machine. To execute on a remote machine you need to combine settings with a Credential.

Execute in x86 mode

This settings is forcing 32 bit execution of your PowerShell script.

PowerShell file

Full path to a PowerShell script.

PowerShell script text

You enter your PowerShell code here which will be compiled and run in real time.

Parameters​

Click on Parameters button to edit parameters. You can parse parameters in the PowerShell script like this:

param (
  [string]$parameterkeyname1 = "mydefaultvalue1",
  [string]$parameterkeyname2 = "mydefaultvalue2"
)
Write-Output $parameterkeyname1

About Script blocks More information about script blocks here: https://www.sapien.com/blog/2019/05/13/advanced-powershell-functions-begin-to-process-to-end/

Starting script​

In general you need to call the function after the script block. For example;

Function Test-ScriptBlock
{
  // function content
} #END Function Test-ScriptBlock
 
// the function call
Test-ScriptBlock -Number 1

Passing parameters​

  1. No parameters
Function Test-ScriptBlock
{
    BEGIN
    {
        Write-Output "In Begin block"
    }
    PROCESS
    {
        Write-Output "In Process block"
    }
    END
    {
        Write-Output "In End block"
    }
} #END Function Test-ScriptBlock
 
Test-ScriptBlock
  1. parameters after function call
Function Test-ScriptBlock
{
    BEGIN
    {
        Write-Output "In Begin block"
    }
    PROCESS
    {
        Write-Output "In Begin block"
        Write-Output "Number value: "         $Number
    }
    END
    {
        Write-Output "In End block"
    }
} #END Function Test-ScriptBlock
 
 
Test-ScriptBlock -Number 1
  1. Value parameters from pipeline
Function Test-ScriptBlock
{
 Param
 ( 
     [Parameter(ValueFromPipeline)]
     [int]$Number
 )
    BEGIN
    {
        Write-Output "In Begin block"
    }
    PROCESS
    {
        Write-Output "In Begin block"
        Write-Output "Number value: "         $Number
    }
    END
    {
        Write-Output "In End block"
    }
} #END Function Test-ScriptBlock
 
 
1, 2, 3 | Test-ScriptBlock
``

4. Using VisualCron parameters into script block

```powershell
param (
  [int]$Number = 0
)
Function Test-ScriptBlock
{
    BEGIN
    {
        Write-Output "In Begin block"
    }
    PROCESS
    {
        Write-Output "In Begin block"
        Write-Output "Number value: "         $Number
    }
    END
    {
        Write-Output "In End block"
    }
} #END Function Test-ScriptBlock
 
 
Test-ScriptBlock -Number $Number

Passing back data from PowerShell​

You can use these PowerShell commands to pass back output to VisualCron:

VisualCron standard output

write-output "my output"

VisualCron standard error

write-error "my error"

Raising/Throwing errors

Use the Powershell throw command to raise errors. By default we are considering the Task fail if it contains any text written to error output. When that happens the standard output is overwritten with debug information. To override this behavior go to On error tab of Task and uncheck Contains error output.

Troubleshooting​

Access denied errors

Ensure PowerShell's ExecutionPolicy = Unrestricted Start Windows PowerShell and type in the command below at the prompt and hit enter. It should return unrestricted. get-executionpolicy

If it returns restricted then enter the command below at the prompt and hit enter. set-executionpolicy unrestricted

If you receive an Access Denied message, then open a ticket with your IT Team to update this for you. You may need to provide the steps below to them: Update the Registry Editor (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell) with a tag for ExecutionPolicy and set it to Unrestricted

System.TypeLoadException

If the ExecutionPolicy is setup correct and VisualCron returns the error below when running PowerShell, ask INFRA to upgrade PowerShell to the highest version available for the respective server. Recently we found that PowerShell v1 on a Windows 2008 SP2 servers will not execute PowerShell from VisualCron so we had to upgrade to PowerShell v2.

AuthorizationManager check failed

Please add/insert the share you are trying to access in the script to "trusted sites".

No output using Write-Host

Write-Host cannot be used in the VisualCron enviroment which is different from Powershell.exe. You need to use Write-Output or Write-Error instead.

Unhandled Exception: System.IO.FileNotFoundException: Could not load file or assembly 'System.Management.Automation, Version=3.0.0.0

You need to install PowerShell 3.0. You can download it here: https://www.microsoft.com/en-us/download/details.aspx?id=34595

How can I use PowerShell 7?

Currently you cannot use it in the PowerShell Task as PowerShell 7 is using .NET Core 3.1 and VisualCron is using .NET Standard 4.8. We might upgrade in the future to .NET Core and then it would be possible. A workaround is to use the Execute Task with the PowerShell 7 exe (pwsh.exe) full path in command field and the full path to PowerShell script in argument field.

CmdletBinding - no processing of PROCESS block

Wrap the script into a fucntion (see example 1).

PowerShell Task hangs

If you are using dbatools in the command:

These links tell about the problem:

https://dba.stackexchange.com/questions/184327/sql-agent-powershell-task-never-finishes http://glenn-pepper.co.uk/2018/11/22/2-issues-with-powershell-steps-in-sql-agent/

You can try one of the solutions;