Write-Output is showing System.Data.DataRow for a variable which get the data from a sql query column value and If statement uses that variable is not executing.

Question

1. We are Using PowerShell in Azure Runbooks to run the azure data factory pipelines automatically.
2. I have written a PowerShell code to stop the execution if previous run failed.
3. below is the code (SQL query result is FAILED in below code) $STATUS_CURRENTRUN = @(Invoke-Sqlcmd -Query "SELECT distinct STATUS_FLAG FROM LOGGING.DM_PROCESS_CONTROL_DETAIL WHERE PROJECT_FLAG='GEMS' ;" @params ) Write-Output "STATUS_CURRENTRUN is $STATUS_CURRENTRUN" if($STATUS_CURRENTRUN -eq 'FAILED') { stop the execution update the log table }
4. Now the problem is Write - Output "STATUS_CURRENTRUN is $STATUS_CURRENTRUN" command displaying System.data.datarow instead of query result (FAILED).
5. And also, if statement in above code is never executing.

Please help me to get it fixed.

Answer 1

Invoke-Sqlcmd returns a collection of DataRow objects, even when there's a single result. You're assigning this entire collection to $STATUS_CURRENTRUN, which is why you're seeing System.Data.DataRow. the following approach can work better for you

# Run the SQL query
$results = Invoke-Sqlcmd -Query "SELECT distinct STATUS_FLAG FROM LOGGING.DM_PROCESS_CONTROL_DETAIL WHERE PROJECT_FLAG='GEMS' ;" @params

# Extract the value (assuming STATUS_FLAG is the desired column)
if ($results.Count -gt 0) {  # Ensure there's at least one result
    $STATUS_CURRENTRUN = $results[0].STATUS_FLAG  
} else { 
    # Handle case where there are no results (e.g., log an error)
    Write-Warning "No results from the SQL query."
}