Sending error codes on DTS failure w/xp_sendmail. Possible?

[ewilkes]

I have a DTS package setup with an email (xp_sendmail) sent on failure alerting me that there was a problem. Is there a way to include the cause of the error in the body of the email?

 

[tlbroadbent]

What kind of task are you executing? Do you want to capture errors from an Execute SQL task, Transform data task, ActiveX task, ...?

I'm not sure exactly how you are using xp_sendmail to notify you of the failure. Normally, we run DTS packages from a SQL Agent job and the Agent notifies us when the Job fails. We create log files from each DTS package and can review the log when the job completes.

Sometimes, we execute a Send Mail task when a particular step of a package fails. I've not used xp_sendmail for error notification unless the error occurs in a SQL procedure.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[ewilkes]

Sorry for not making it more clear. I would like to capture errors from an Execute SQL Task (a Stored Procedure). I have a step caled sendmail_failure which is:
Declare @Msg VarChar(32)
Declare @Sub VarChar(32)

Set @Sub = 'DTS Package Failure' + ' ' + Cast(GetDate()as VarChar)
execute master.. xp_sendmail @Recipients = 'myemail@address',
@subject = @Sub,
@message = 'Sproc_DataMerge: Update Failure'

What I'm hoping to do is send any error information in the message body portion of the mail. Since the job runs at 3am I want to do as little searching for the problem as possible. Thank you.