Returning the current SSIS Error Count

Question

Within an SSIS package there is the MaximumErrorCount property. The useage of this property implies that during the package execution, the number of errors are being counted by the package.

At various points during the execution of a particularly complex set of packages (which typically have MaximumErrorCount set to higher levels at various points) I would like to be able to capture the current error count - but I can't seem to find a mechanism to do this.

Ideally it would be a system variable (it isn't as far as I can see) so I could build expressions against it at will, but worse I can't seem to find a means to capture it in script either (my VB.net skills are poor, so I may have missed it, but I've googled and searched in the Object Browser without success).

Any SSIS Guru's know how I might capture the current error count at given points during a package's execution?

(I have tried embedding a simple variable increment into the package-level OnError event handler, but on rare occasions this seems to get out of synch with the actual number of errors "counted" by the engine. And in any case, if there is a built-in counter, that would be greatly preferred and far more elegant)

Thanks in advance.

Answer 1

Usually, you would use constraints to direct the flow of error handling. i.e. if a particular task fails, direct the control flow with a failure constraint. If you want to create a tolerance based on the number of errors, you would need to capture each error in a variable after each task (again, using constraints and a script task).

I don't think there is a counter that is exposed which holds the current number of errors for a task or container, but such a counter would be scoped to each object and the package itself. This sounds like it would be painful to work with even if you could get at it.

My suggestion would be use things out of the box as best you can:

• Use constraints to direct Success and failure behavior
• Do not use event handlers, because they are like an invisible GOTO and you never know where they are
• keep working the code so that it succeeds rather than tolerates errors. It is scary to raise the max error count and never know anything went wrong. I mean unless you don't care about your data. I am not being facetious in that remark - this might be perfectly acceptable behavior in migrating data to a test environment.
• Use variables sparingly. There are probably a few key points in some of these packages that need attention, so don't go adding a maze of variables that will become even more difficult to troubleshoot and maintain. If you can lower the max error count down to 1 over time, you'll probably be only changing a few points in the packages (80/20?). If you really have to rewrite the whole thing, well... then it was crap to begin with and good riddance, right?