send email when the package fails to run

Question

send email when the package fails to run

bladerunner148

SSCommitted

Points: 1824
More actions
December 3, 2008 at 12:17 pm

#154979

Hi,
I have a SSIS package that is scheduled to run every midnight. Is there a way to send an email to a particular user when the package fails to run? I know there is a Notification on the package, I tried once but it didn't send anything. Is there an article that explains the step by step process?
Thanks!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

raj acharya SSCrazy Points: 2805 More actions · Answer 1

hi yes ofcourse there is a way to send mail notification when your package failed, goto business development studio , open your dts package there from their you can set mail notification when your task failed... try that and if you can't find then reply me

Raj Acharya

bladerunner148 SSCommitted Points: 1824 More actions · Answer 2

I have opened the package in Business Intelligence Studio. On the properties, i see a FailPackageOnFailure and FailParentOnFailure properties but I do not see Notification anywhere. Could you tell me where that is? I thought I would have to do that on the Sql Server Agent by right clicking on the package, going to Notifications and set up the email. I tried that and that didn't work for me.

niall.baird Hall of Fame Points: 3468 More actions · Answer 3

You could build an email task in the event handler for 'on error'. I did originally have seperate email tasks depending on which task failed, but found it better to have a (global) variable 'sMessage', and a script task that added the source name & source description to the variable, which the sendmail task picks up.

Basically my process is this:

Have a table called SSISErrorLog

Create an Event Handler for OnError (click on the event handler tab, should be self-explanatory from there)

Create an Execute SQL task:

INSERT INTO [BS_PROD].[dbo].[SSISErrorLog]

([PackageName]

,[SourceName]

,[SourceDescription]

,[Event]

,[EventDate]

,[ExecutedBy])

VALUES

(?

,?

,'Failure of Monthly PV Data Load'

,getdate()

,user_name())

Create a script task that takes a r/w variable "sMessage"

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

'

' Add your code here

'

Dim sMessage As String

sMessage = "There has been an error executing the Monthly PV Data Load SSIS package. Please see the error log " & _

"for more details. 'SELECT * FROM dbo.SSISErrorLog ORDER BY EventDate DESC'"

sMessage = sMessage & vbCrLf ' & dts.pipeline

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

Finally, create a SendMail task that uses @[User::sMessage] as the message source.

save & close.

Tim Mitchell SSCoach Points: 15681 More actions · Answer 4

If you want notification when the entire package fails, it's easiest to create a notification on your SQL Agent job to send an e-mail upon failure.

hth,

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

raj acharya SSCrazy Points: 2805 More actions · Answer 5

goto business development studio.... then open new integration service project in toolbox you will found send mail task .... now first create your package and in between put this task of send mail ...you need to configure this send mail task like insert smtp and other things that you can do through right click on send mail task and from edit ... first drag and drop send mail task to between your packages.... if further help need post me reply

Raj Acharya

bladerunner148 SSCommitted Points: 1824 More actions · Answer 6

Hey Raj,

I added the Send Mail Task to the package. The way I have linked that to the control flow is:

Execute Sql Tasks -> Data Flow -> Send Mail Task

Under Send Mail task Editor:

MessageSourceType = Direct Input

MessageSource = ''

Priority = Normal

Does this look right?

niall.baird Hall of Fame Points: 3468 More actions · Answer 7

Make sure the link between your sendmail and the preceding task is set to "Failure"

raj acharya SSCrazy Points: 2805 More actions · Answer 8

hi you have to configure smtp connection for that if you have your company mail id then you can do that easily, from database mail or from sql mail . once configure that into your outlook profile and then test through database mail and one another thing you can do is to send net send message to server through using xp_cmdshell

Raj Acharya