SSIS Execution Process Task StandardErrorVariable property is empty

Question

I have a SSIS Execution Process Task that runs a Powershell script that I'm trying to utilize in the custom logging framework that I'm building. Everything I've researched online has said that if I capture StandardErrorVariable and StandardOutputVariable in variables that I should be able to use these to derive meaningful error messages in the custom logging framework that I'm building. The Powershell script pulls data from a API Endpoint and moves it into a table in SQL Server. In the Powershell script I changed one of the names in the insert statement to be a column that doesn't exist to test what the error messages look like in these properties. StandardOutputVariable is returning what I would expect but StandardErrorVariable is blank. Can anyone help me figure out why StandardErrorVariable is blank?

Answer 1

It's probably because of too many shells involved. Plus SSIS is pretty buggy itself. As a possible workaround you can try to redirect your error stream to stdout, probably it will appear in StandardOutputVariable. From inside powershell add *>&1 , although you might need to wrap your script as a scriptblock first:

&{

#some script

Write-Output "Hello"

Write-Error "Error occured"

} *>&1  # * will move all streams to stdout

If you call you script from batch/cmd add 2>&1 at the end (* will not work in cmd, 2 is error stream)

> powershell .\yourscript.ps1 2>&1

Also, you can do something similar by wrapping your script with try/catch, and print error in catch using Write-Host. If you really need to keep errors separetly and no other solution, probably redirect it to file and then read it from there (by adding 2>error.log)