sql server dts: Hi:

I have scheduled a DTS package (SQL Server 2000) and it is able to
send an email upon completion. However, I would like to customize the
subject and message body of that email. For example:

MyDTSPackageName Failed at 10/8/2007 9:43 a.m.

Body:
MyDTSPackageName Failed at 10/8/2007 9:43 a.m., please contact Bill
at ....

It just gives a default message currently. How to do?

Thanks,
Kayda

I would suggest adding another Execute SQL task, at the end of your DTS
package.
This piece will notify of success (so should execute on successful
completion of the preceding task):
declare @v_msgstr as varchar(1000)
declare @v_recipients as varchar(100)
declare @v_copy_recipients as varchar(100)
declare @v_subject as varchar(100)

set @v_recipients = 'your recipients'
set @v_copy_recipients = 'whomever else'
set @v_subject = 'your subject plus date '+cast( getdate() as
varchar(100) )
set @v_msgstr = 'Your custom message body...'

EXEC master.dbo.xp_sendmail
@recipients = @v_recipients,
@message = @v_msgstr,
@copy_recipients = @v_copy_recipients,
@subject = @v_subject

Similarly, you can have another task to handle failure.

So this is not a customized SQL job notification, but rather addition to
your DTS package which should accomplish what you are looking for.



[quoted text, click to view]