developer.* Blogs - My Issues With SSIS Event Handlers (And Why I'll Use Them Anyway) - Comments

SSIS task constraints

Guest — Thu, 08 Feb 2007 15:06:54 -0800

The "On Completion" Task constraint appears to work fine for "Excecute SQL" Tasks, but not for "Data Flow" Tasks. A couple of suggestions that worked for me:

1) Of course, set the Task Properties of to not fail the Package on failure of the Task.
2) Set the Max # of allowed errors on the Task to some very large number (see Task Properties).
3) Set the Task to "Delay Validation" (this setting is also found under Task Properties).

After taking these three steps, my "Data Flow" Task fails with an "On Completion" constraint, but the Package succeeds. IE, once the Data Flow Task is done, the next Task continues. Just like you'd think it would intuitively.

I think this same behavior works with Task Containers.

Hope this makes sense.

Hiding Errors To Allow Package Success

Craig — Tue, 15 Aug 2006 05:56:57 -0700

I'll admit I have found getting to grips with Event Handlers a very frustrating experience due to the lack of control inherent in the design. I like the fact that events only need to be defined at the very top level except when, as inevitably happens, you want to deviate from this.

One problem I'm having at the moment is with error capture. I have an Execute SQL Task that calls a stored procedure on a DB that may be unavailable. I don't care whether this fails or not as it's just logging. However, I do care that any failure will not cause the package to fail.

On the task I can force the execution result to Success (this visibly works at design and debug time). However, the top (package) level OnError Event Handler still fires. This indicates the error is still being detected despite the task being forced to exit with Success. Well that's OK as I'd still like to see the error in DTExec output. The problem is the final return from the Package is still Failure despite the aforementioned task Success.

I can find no way of stopping this final Failure. I can't force success at the package level because I do want some errors to bubble up and report a package failure.

What's most infuriating about all of this is reading the properties as allowing one thing while SSIS continues to do something different. Most of the problems are down to the fact I find the properties ambiguously or poorly named.

SSIS is a nice idea poorly implemented.

Like you however, I'll still continue to fight with SSIS and it's event handlers. It's more down to needs must rather than desire.

Related Discussion at Jamie Thomson's Blog

Daniel Read — Thu, 19 Jan 2006 17:24:42 -0800

Follow this link to read a great response to this post from Jamie Thomson. There is some follow on discussion there also.

Best,
Dan

My Issues With SSIS Event Handlers (And Why I'll Use Them Anyway)

Daniel Read — Wed, 18 Jan 2006 17:54:21 -0800

In this post I will document some of the observations and conclusions I have come to in the process of hammering on the event handler functionality of SSIS, trying to see how it ticks, making sure I understand I can use it properly in my system, which is a multi-package system with some specific logging, exception handling, and monitoring requirements.