MSDN Student


I have a flat file component in a package that is reading a comma separated file.

It expects two columns of int type.

I have an on error event for the entire package. The On error event has one script task which just logs to the windows event log.

if I corrupt the input values in the file, for example instead of a number I put a character

When I debug the package, I see that the script task in the on error event blinks 7 times and I get 7 errors in the windows event log.

Now If I put another script task next to the flat file source and attach it to the flat file source as a error output (with row redirect).

I get only one error message.

Why are the two behaviours different Both are error handlers one is at the component level the other is at the package level.

So why is it that the package level is invoked 7 times if the input is corrupt but the component level is invoked only once.

regards,

Abhishek.




Re: Understanding Error Handling in SSIS

jwelch


Rows handled through error redirection don't raise errors, so the OnError event handler will not fire. If you think about it, this is the desired behavior. Rows handled with error redirection are considered to be "handled" errors - you are telling SSIS that you are dealing with the error rows.

The reason you get multiple errors when you don't redirect error rows is that one root error (bad data in your row) can trigger a series of errors (the original error, one on failure to process the file, one saying the flat file source failed, and errors about the thread shutting down). They are all a result of the first error.







Re: Understanding Error Handling in SSIS

MSDN Student

Thanks.

Is there any way to surpress the multiple error messages (other than handling the errors in SSIS).

Reason is that I maintain a set of SSIS packages written by developers sitting in some other country. If they don't handle the error messages in their code properly.

Then sometimes, one error leads to multiple errors being logged in the windows event log which leads to multiple alerts being generated by our data center managment software. This discomforts the admins because they want to receive only one alert for an failure.

One obvious approach is that I send all the packages back to all the developers. But this would be very time consuming and expensive.

Can I make sure by some configuration that the series of errors are handled as one

regards,

Abhishek.






Re: Understanding Error Handling in SSIS

jwelch

Not through a configuration. You would have to handle it in the event handler - a custom script might be easiest.




Re: Understanding Error Handling in SSIS

MSDN Student

> You would have to handle it in the event handler - a custom script might be easiest.

Are you saying that I can write a custom script in the event handler and surpress multiple events (without redirecting the errors at each and every file data source)

If yes, how can I do this this will help me... because no matter how negligent the programmers are, I will still get one error in the event log.

Sorry for asking again, but it will be kind if you could eloberate more.

regards,

Abhishek.





Re: Understanding Error Handling in SSIS

jwelch

It depends on how you are handling logging. If you use a custom log provider, you can code it to do whatever you want, including ignoring some or all of the errors raised. Or, if you control logging by using a custom script task in the OnError event, you could code it to not log the error.

However, as far as I am aware, you can't cancel an error that has occurred, so if you are using built-in logging, this won't work.






Re: Understanding Error Handling in SSIS

MSDN Student

> If you use a custom log provider, you can code it to do whatever you want, including ignoring some or all of the errors raised

I thought about this idea. But it doesn't seem very easy to implement.

There is no co-relation between the 7 exceptions which are thrown. So the only way to ignore the other 6 exceptions is, to create a SQL table which maintains which exceptions are primary and which are secondary.

However, the problem is how to make sure that this SQL table is complete so that all types of exceptions and their sub exceptions are listed here. So that all types of sub-exceptions can be filtered out.

Is there a generic way to determine which is the first exception and which ones are sub-exceptions

regards,

Abhishek.





Re: Understanding Error Handling in SSIS

MSDN Student

bump.

How will my custom logger know whether an exception being logged in the on-error event is primary or secondary

regards,

Abhishek.





Re: Understanding Error Handling in SSIS

jwelch

Don't you want just the first error from the package Just increment a counter each time an error is raised and only log the first one.




Re: Understanding Error Handling in SSIS

jwelch

I posted an example of doing some custom error handling on my blog:

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/05/handling-multiple-errors-in-ssis.aspx

Hope this helps.