By: Tim Smith | Comments | Related: > PowerShell
Problem
We use several third party tools that use PowerShell functions, along with other languages for running functions along with testing functions. These scripts can involve connections to databases, running unit tests, validating data, among other functionality. We want our execution of these scripts to provide us with an option that we select to fail, caution us with what the failure was, or pass even if the script fails.
Solution
When we run scripts, we'll generally want to report a failure with an exit. However, there are situations where a failure in the script may be expected, or we may be testing and want the next step to occur, even if there's a failure. Building an override can be useful and as we'll see, we can create a re-usable function for this that we can strategically place in situations that call for its use.
Whether we use custom software we develop or we use a tool that reads a script's output, we want to consider how the output from a script is read. As we'll see in this tip, we have a few options for how we can handle errors - fail, caution, pass while reporting that it actually failed, or do a combination of the latter two while ending in failure after passing other tests. The below are examples where we may want to use one of these all involving the same scenario, but different contexts - an ETL job that extracts data from a source:
- Fail. When the ETL job attempts to connect to the source and it fails, the script reports a failure.
- Caution. When the ETL job attempts to connect to the source and passes, but the data are not available, so it cautions that data are not present, thus it will try again.
- Pass reporting a failure during a run. We're testing the ETL job and we want to see if all our steps function, even if we don't have some of the pieces in place, so we pass even after a failure to see if other steps execute correctly. Another context of this would be if sometimes a data source isn't supposed to be online (ie: expected random behavior) and we pass a script that fails a connection because it simply means that the day meant there was no data. In other words, a "failed connection" was possible.
- Pass even after a failure, but end in failure. Similar to the first example in the previous scenario, except that we eventually want the script to fail at the end if one failure (or more) was experienced after validating all other steps.
Using PowerShell ISE, we can compare the output of the following three functions - Write-Output, Write-Warning and Write-Error. In this tip, we'll use these three functions in our call, though we can use Write-Output for these and pass in different parameters.
$message = "This is our returned message." Write-Output $message Write-Warning $message Write-Error $message
For re-use, we'll create a function that we can call in the catch block of other functions. We'll take two parameters - a message parameter that accepts a string and a set parameter that will determine the result output of the function. When we want the function to fail as we see in the above example, the function will abruptly stop, whereas a caution and pass will continue. For this function, the result possibilities will be fail (terminate script), caution (continue script with an alert) and pass (continue script) and for this parameter we'll use the validate set accepting either fail, caution or pass. Since both of these parameters are required, we'll set the mandatory for both of the parameters to be true.
Function Catch-Options {
Param(
[Parameter(Mandatory=$true)][string]$message
, [Parameter(Mandatory=$true)][ValidateSet("Fail","Caution","Pass")]$outputoption
)
Process
{
switch ($outputoption)
{
"Fail" { Write-Error $message }
"Caution" { Write-Warning $message }
"Pass" { Write-Output $message }
}
}
}
With our function, we will now call our function inside other functions and test this. In the below script, we execute 2 functions that each have our created function inside of them and intentionally set them both to fail - in the first script, we don't enter a server name and in the second script we don't pass in a file that exists to read. We see how both of these functions will report cautions and passes even though they failed. Since a failure would exit the script and we see the failure is set to default in both scripts, we don't test this in the when calling these scripts.
Function Catch-Options {
Param(
[Parameter(Mandatory=$true)][string]$message
, [Parameter(Mandatory=$true)][ValidateSet("Fail","Caution","Pass")]$outputoption
)
Process
{
switch ($outputoption)
{
"Fail" { Write-Error $message }
"Caution" { Write-Warning $message }
"Pass" { Write-Output $message }
}
}
}
Function Execute-Sql {
Param(
[Parameter(Mandatory=$true)][string]$command
, [Parameter(Mandatory=$false)][ValidateSet("Fail","Caution","Pass")]$outputoption = "Fail"
)
Process
{
$scon = New-Object System.Data.SqlClient.SqlConnection
$scon.ConnectionString = "Data Source=;Initial Catalog=master;Integrated Security=true"
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $scon
$cmd.CommandTimeout = 0
$cmd.CommandText = $command
try
{
$scon.Open()
$cmd.ExecuteNonQuery()
}
catch [Exception]
{
Catch-Options -message (("(" + $MyInvocation.MyCommand.Name).ToString() + ") " + $_.Exception.Message) -outputoption $outputoption
}
finally
{
$scon.Dispose()
$cmd.Dispose()
}
}
}
Function Read-FullFile {
Param(
[Parameter(Mandatory=$true)][string]$file
, [Parameter(Mandatory=$false)][ValidateSet("Fail","Caution","Pass")]$outputoption = "Fail"
)
Process
{
try
{
$readfile = New-Object System.IO.StreamReader($file)
$readfile.ReadToEnd()
}
catch [Exception]
{
Catch-Options -message (("(" + $MyInvocation.MyCommand.Name).ToString() + ") " + $_.Exception.Message) -outputoption $outputoption
}
finally
{
if ($readfile -ne $null)
{
$readfile.Dispose()
}
}
}
}
Execute-Sql -command "EXEC stp_OurProcedure" -outputoption Caution
Execute-Sql -command "EXEC stp_OurProcedure" -outputoption Pass
Read-FullFile -file "C:\InvalidFile.txt" -outputoption Caution
Read-FullFile -file "C:\InvalidFile.txt" -outputoption Pass
We can also see a few key details in both of the scripts that we intentionally passed invalid parameters. Our try-catch statements are designed to catch when execution occurs, but it's possible that I pass in an invalid parameter to the connection string that will throw a failure since setting the connection string isn't in a try-catch block. We also see something similar with our Read-FullFile script with the dispose: note that we only call dispose if the object of $readfile exists (meaning the object is not null). If the object failed creation, it would throw an error when trying to dispose a non-existent object.
As a reminder, if we have methods or are executing actions outside a try-catch block, this function won't assist us with handling failures, cautions and passes. We must wrap all executions in try-catch statements so that we can control the output. As an example of this, suppose we have a tool that runs PowerShell scripts as long as a script hasn't thrown an error - the tool would terminate on the first attempt to divided by zero and never reach the second attempt, even if the second attempt wouldn't throw an error. For this, we want all method calls and executions to be within a try-catch.
Write-Host (11/0).ToString()
try
{
Write-Host (1/0).ToString()
}
catch [Exception]
{
Write-Host $_.Exception.Message
}
Next Steps
- A script context where a failure should abort a script and raise concerns should always use the failure approach.
- The caution and pass can be useful when a failure might be expected behavior and there may be steps we still want executed, while retaining the results of what happened in the attempt that failed.
- In testing scripts, the function we created can be useful to pass over failures during testing. Once we've validated this, we can then revert the calls to fail.
- In some contexts, reporting errors with detailed information should be avoided due to security risks associated with errors. While we may find it appropriate to use this function in testing and validation, this should be removed in higher environments with strict security requirements. Errors provide information that can be used inappropriately, so we should consider this in contexts where that inappropriate information may invite risks and costs.
About the author
Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
