October 16, 2009 at 2:31 pm
Do you know if the on Failure condition works?
When the condition is 'Success' it sends the mail notification but it doesn't when the condition is 'Failure', even though I am making the task fail.
October 16, 2009 at 2:36 pm
What type of task is failing? I assume then that the task fails and fails the package? Have you tried putting the e-mail notifiction into the OnError event handler?
October 16, 2009 at 2:54 pm
I created a sql task where I am simply truncating a table. The FailPackageOnFailure is set to True.
On purpose I have given the sql an invalid table name. The task then fails.
The precedence constraint Value is set to Failure.
When I run it, the task fails but no failure mail is sent.
If I let the task succeed and the constraint Value is set to Succeed, the email is sent.
October 16, 2009 at 2:58 pm
I don't know how to put the e-mail notifiction into the OnError event handler.
October 16, 2009 at 8:31 pm
Maybe, because the Fail Package On Failure is set to true, maybe the whole package is failing as soon as that step fails so it never gets to the step where it sends the mail? Have you tried it with that set to False? Yes, I'm grasping at straws and don't really know, but it makes a certain kind of twisted sense...
October 17, 2009 at 7:10 pm
It may seem counter-intuative, but the old DTS worked this way too. A failed task fails the package which means that no further tasks will be ran. As soon as your TRUNACATE step fails, the package stops executing so the failure prececence constraint is never evalutated.
I'm not sure if changing the FailPackageOnFailure setting changes this or not. You could try changing it to false, but that would impact how the rest of your pacakge behaves as well. I would recommend trying to put your e-mail task in the OnError event handler. To do so, follow these steps:
1. Left click the truncate table task so that it has the focus set on it.
2. Go to the Event Handers tab.
3. Make sure the Event Handler drop down box is set to OnError.
4. Click the blue link entitled: "Click here to create an 'OnError' event handler....".
5. Drag the Send Mail task onto the screen and configure it as needed.
October 18, 2009 at 9:34 am
Thank you both so much. That was exactly correct. The event handler worked great. With this, however, it looks like for each and every task, I will have to create an event handler. Is there a better way to do this?
Thank you, again.
October 18, 2009 at 9:41 am
One more question, how do I spit out the servername and userid (if possible of who is executing the pkg.) to my log.
I have created SQL Serverer package configuration and all my errors and information are written to the [SSIS Configurations] table. I would like to somehow write the servername and userid to this table, each timethe package is executed.
October 19, 2009 at 12:14 am
If you've had this set up before and its worked sweet as a peach, then it could be the an issue with the mail server, e.g. work email or hotmail etc.. which ever is your provider check they're working.
October 20, 2009 at 7:28 am
MelissaLevitt (10/18/2009)
Thank you both so much. That was exactly correct. The event handler worked great. With this, however, it looks like for each and every task, I will have to create an event handler. Is there a better way to do this?Thank you, again.
If you go into eventhandlers and then use the drop down on the left called executable you can scroll this up to the package root. Eventhandlers set here will fire for all child events.
You should bare in mind that this will fire for every error etc the package encounters so if you have your email in here you could get a lot of emails.
There is an article here:
http://www.sqlservercentral.com/articles/SQL+Server/66387/
about event handlers. I would use parameter mapping rather than the method shown here though as the error description will ofetn come back with sql syntax and so produce a syntax error.
Seth
October 22, 2009 at 9:27 am
This is very helpful. Thank you!!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply