Building data tests in PowerShell

Data tests in PowerShell

$results = New-Object System.Data.DataTable
 $sheetName = New-Object System.Data.DataColumn sheetName,([string]) # Name of worksheet these results will be stored in. 
 $status = New-Object System.Data.DataColumn status,([string])
 $output = New-Object System.Data.DataColumn output,([string])
 
 # Define table columns
 $results.Columns.Add($sheetName)
 $results.Columns.Add($status)
 $results.Columns.Add($output)
 

$testResults = Get-ImportStatus `
  -sqlConnection $sqlConnection `
  -testThreshold 0 `
  -testQuery "SELECT count(1) FROM [config].[IngestionProcessLog] WHERE [Error_Process] IS NOT NULL;" `
  -successMsg "No Errors encountered during processing:" `
  -successQuery "SELECT [Source_Process],[Process_Status],[Log_DateTime] FROM [config].[IngestionProcessLog] where [Error_Process] IS NULL;" `
  -errorMsg "The below Errors Have been recorded:" `
  -errorQuery "SELECT [Source_Process],[Process_Status],[Log_DateTime] FROM [config].[IngestionProcessLog] where [Error_Process] IS NOT NULL;"
 

function Get-ImportStatus {
 param (
  $sqlConnection,
  $testThreshold, # Test query number that the results are failed at. 
  $testQuery, # Should return a single number
  $successMsg,
  $errorMsg,
  $successQuery,
  $errorQuery
 )
 #
 # This procedure runs the testQuery sent in and passes/fails the results based on the TestThreshold. 
 # Returns a hashtable of results
 #
 
 [hashtable]$row = @{}
 
 $testStatus = Test-DataStatus -SQLConnection $sqlConnection -Query $testQuery # This just runs the SQL Query
 
 if ($testStatus -gt $testThreshold) {
  $row.status = $errorMsg
  $row.output = Get-ImportData -SQLConnection $sqlConnection -Query $errorQuery
 }
 else {
  $row.status = $successMsg
  $row.output = Get-ImportData -SQLConnection $sqlConnection -Query $successQuery
 }
 
 return $row
 
 }
 

$row = $results.NewRow()
 $row.sheetName = "ProcessStatus"
 $row.status = $testResults.status 
 $row.output = $testResults.output
 $results.Rows.Add($row)