How to expose an executable error as a message in SSISDB

Question

We are currently calling an executable built using python from a SSIS Execute Process Task run as part of a nightly batch job. This all works fine however when the executable fails only a very basic generic error message is raised with no reference to the true underlying error. When the executable is run via the command line the underlying error correctly returns.

Is there anyway that we can bubble up the underlying error of the executable when called via a SSIS Execute Process Task and have it written or logged to the SSISDB Catalog?

Please see the existing error below and the ideal error that should be returned (and is currently returned when running from the commandline).

Answer 1

There is no option available on an Execute Process Task that will redirect the output when it fails and bring that back out in logging.

To accomplish that you will need to:

• Defined a variable to capture the output
• Set that in the StandardOutputVariable option on the Execute Process Task
• Under the event handler for that task, create an "OnTaskFailed" event handler and use a script task to return back out the output

Example:

String variable called "User::exe_output" which is then added the StandardOutputVariable on the Execute Process Task:

The documentation states:

StandardOutputVariable Select a variable to capture the output of the process, or click to create a new variable.

Then under event handler:

• Add an "OnTaskFailed" event on the Execute Process Task
• Add a script task

Open the script task and add the "User::exe_output" variable as ReadOnlyVaraibles:

Edit the script and add the following code:

public void Main()
{
    //Just assigning the exe_output to a local variable
    string exe_error = Dts.Variables["User::exe_output"].Value.ToString();

    //Checking it its blank
    if (exe_error != "")
    {
        //This brings back out whatever was captured in the output of the execute process task.
        //Depending on how you want it logged, warning or an error, either one will log the output.

        //Dts.Events.FireError(-1, "Execute Process Task Error", exe_error, String.Empty, 0);
        Dts.Events.FireWarning(-1, "Execute Process Task Error", exe_error, String.Empty, 0);
    }
    Dts.TaskResult = (int)ScriptResults.Success;
}

Then when the process runs and fails you still get the original message, but now you also get what was captured from the output of the Execute Process Task:

In my example I was just trying to copy a file that didn't exist.