Send an email on non-fatal errors from Data Flow

Sandy F.

Ok, I'm incredibly new to all of this so please bear with me. I took the Integration Services tutorial, and that's pretty much all that I have done.

I'm creating my first package and I want to be able to email myself when certain non-fatal conditions occur. I still want the entire row to flow into my table.

My understanding is that this type of checking should be done in the "Data Flow" tab not the "Control Flow" tab. However, I can't see a toolbox item in "Data Flow" to create a SQL script like this. I can see that the "Lookup" toolbox item will allow me to write a SQL script but how do I get it to send out the email Or should I really be doing this type of checking in "Control Flow" where there is an "Send email task" item

Any help is greatly appreciated! Thanks!

Bob Bojanic

Hi Sandy,

you should try to use Event Handlers. Event Handlers will allow you to build additional control flows that get activated by events (OnError, OnWarning, etc) posted from executables (a data flow in your case) you are monitoring.

HTH.

Phil Brammer

Bob Bojanic wrote:

Hi Sandy, you should try to use Event Handlers. Event Handlers will allow you to build additional control flows that get activated by events (OnError, OnWarning, etc) posted from executables (a data flow in your case) you are monitoring. HTH.

Bob, an error handler shouldn't fire (should it ) if Sandy is redirecting error rows in the data flow.

Bob Bojanic

Phil Brammer wrote:

Bob, an error handler shouldn't fire (should it ) if Sandy is redirecting error rows in the data flow.

It shouldn't, but from her message I think she is more after OnWarning and OnInformation events.

Thanks.

anderdw2

I'm having the same problem. I'm using a conditional split to send rows on the main data path and a set of invalid data to a flat file. I'd like to send notification to a user if rows are sent to the flat file. However, I do see where you can select any of these events as fire points.

Dave

Phil Brammer

anderdw2 wrote:

I'm having the same problem. I'm using a conditional split to send rows on the main data path and a set of invalid data to a flat file. I'd like to send notification to a user if rows are sent to the flat file. However, I do see where you can select any of these events as fire points. Dave

What I would do is add a row count transformation between the conditional split and the flat file destination. Then, back in the control flow, hook the data flow task up to a send mail task and then using precedence constraints you can check the value of the variable that was populated in the row count transformation. If it is greater than zero, send an e-mail.