December 3, 2008 at 12:17 pm
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!
December 3, 2008 at 12:32 pm
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
December 3, 2008 at 1:30 pm
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.
December 3, 2008 at 2:56 pm
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.
December 3, 2008 at 8:03 pm
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
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
December 4, 2008 at 3:33 am
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
December 4, 2008 at 7:33 am
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?
December 4, 2008 at 2:06 pm
Make sure the link between your sendmail and the preceding task is set to "Failure"
December 5, 2008 at 3:40 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply