3

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.

Improve this question
3
  • If you don't agree with the answer below then you have to build your own logging logic since i don't think there are other option provided by SSIS. Commented Mar 26, 2019 at 10:39
  • I found an easy way -- just use the built in 'red/green' arrows (precedence contraints). One goes to an Expression Task, so does the other. Set the variable there. While SSIS doesn't expose this in a system variable or property, it "exposes it" with its precedence constraints themselves. While you technically can "dig in" and grab the actual variable in a Script task using super complicated syntax, why bother. Commented Mar 26, 2019 at 15:45
  • Then you implemented your own logic Commented Mar 27, 2019 at 8:02

1 Answer 1

Reset to default
1

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

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:

Update 1

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

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Hmm I'm not certain execution status is the same as executionresult -- at least, the latter is not mentioned in those articles. But, it may be more detailed or functionally the same. Hmm. I can try it to see what happens. That seems more of a "this is the current status" looping list rather than "the ultimate result" but it can be used effectively potentially.
Sadly execution_value is even further removed from execution result. It generates random data based on the last task that set it (such as number of inserts for an execute SQL statement task). I found a simple solution for now. Simply do a succeed condition to "Expression Task" that sets a Variable to 1. Then a fail condition to "Expression Task" that sets a Variable to 0. There. SSIS doesn't expose the ExecutionResult natively, but using their built-in workflow, the answer is right there. These duality Expression Tasks can than rejoin into a single flow (Or condition) and proceed from there.
@user45867 have you tried the event handler approach??

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.