Capturing Snowsql return code within a powershell script

Question

I am using a powershell script to run a sql file using SnowSQL. My idea is to use the powershell script as a generic wrapper to run various sql files with different input parameters, executed from jobs scheduled in the enterprise scheduler.

Below is a snippet from the powershell script file:

$Command = '&"C:\Program Files\Snowflake SnowSQL\snowsql.exe"' + ' --config "' + $ConfigFilePath + '" --filename "' + $sFilePath + '\' + $sFileName + '.sql"' + $sParams

Invoke-Expression -Command $Command

The sql file being executed through Snowsql contains several batch statements. My question is, how can i capture the return code from the execution of the file. Particularly, an error code even when at least one of the batch statements inside the sql file fails (not just the status of the last batch statement in the sql file).

I found the below from Snowflake community questions. But, when i use it after the Invoke-Expression line in the powershell script, all i get is the string printed as is.

echo %errorlevel%"

Please help.

EDIT 9/11/2020 I learnt that the equivalent to %errorlevel% in powershell is $?. So, i have added $? after the Invoke-Expression line in my powershell script file. But, it is always returning TRUE (meaning success) even when the snowsql script fails with an error.

Any thoughts please?

Thank you. Srini Seelam

Answer 1

Finally, after a little more research, i found the answer.

$LastExitCode will provide the exit code from the snowsql execution. And, in order for this to work properly, we have to set the "exit_on_error=true" option in the snowsql config file or pass it as a command line parameter.

My code now looks like below

$Command = '&"C:\Program Files\Snowflake SnowSQL\snowsql.exe"' + ' --config "' + $ConfigFilePath + '" --filename "' + $sFilePath + '\' + $sFileName + '.sql"' + $sParams

Invoke-Expression -Command $Command

Write-Host "Snowsql returned exit code: $LastExitCode"

exit $LastExitCode