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).

Have you tried using the OnError handler in SSIS to see what message gets exposed there? — Tab Alleman, Aug 22 '19 at 13:07
I would perhaps play around with the StandardErrorVariable or StandardOutputVariable options, and have an `OnTaskFailed` handler that reports the value of that variable (hopefully the error message). This would also depend on how your EXE is exiting and reporting exceptions. — Chris Mack, Aug 22 '19 at 13:28

score 3 · Accepted Answer · answered Aug 22 '19 at 15:59

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.

This works well! I ended up using the StandardErrorVariable to capture the executable error and bubble it up through a script task using the OnTaskFailed event handler — jeremyh, Aug 23 '19 at 15:33

How to expose an executable error as a message in SSISDB

1 Answers1

Linked