SSIS - capture "ExecutionResult" / "Data Code" in Variable?

Question

It seems SSIS has some unexposed variables called execution_result in the SSISDB/ Catalog under internal.executable_statistics.

But it's also referred to as DataCode in some places but it's essentially for each executable (so task or package level, etc).

0 = success, 1 = failure, 2 = complete, 3 = canceled.

I'm rolling some custom logging just as a simplified/ understandable view of my ETL tasks in SSIS to use in tandem with the more 'kitchen sink' logging in the SSIS Catalog 'internal' schema in SQL Server.

There are no system variables that seem to mimim/ capture ExecutionResult for any particular executable. Is there any way I can capture this and write it to a table or save it in a variable?

I've seen some try to use it in a Script Task that had to reference .dtsx filepath etc --- seems pretty complicated but anyone know an elegant way?

Essentially, I want a logging level (custom written) that is essentially: Package XYZ, blah, blah, blah, blah, executionResult: 1

Again I'm aware this is in executable_statistics, but not how I like it, and I only have SQL Sever 2012 currently so can't customize too much either.

Essentially I want to know if a package ultimately "succeeded" or "failed". Errors are on the right track, but not the same. Since it's possible a package (or container) can have an error and still succeed ultimately.

I suppose I could jerry-rig two separate Execute SQL tasks depending on if a package/ container "succeeded" or "failed" and go from there. Hmm.

Answer 1

ExecutionResult is available when using Event Handlers (on OnExecStatusChanged event handler):

• System Variables for Event Handlers

So, on each task you can add an OnExecStatusChanged event handler and the @[System::ExecutionResult] variable will be available.

If you are new to Event handlers you can refer to the following articles:

• SSIS Event Handlers Basics
• Event Handlers in SSIS
• Integration Services (SSIS) Event Handlers

---

Update 1

You can also benefit from the ExecValueVariable property of each task:

• ExecutionValue and ExecValueVariable in SSIS
• Have you used the ExecutionValue and ExecValueVariable properties?