DTS

[lisa626]

We have a DTS process that runs, I have it scheduled to run every morning, I am trying to set up the notification so that it will send me an email upon either/and/or successful completion or errors. Help please.

 

[AlexCuse]

For a generic notification you can use a 'SendMail Task' (it looks like an envelope in task section of the DTS designer).

Set one up to deliver message x on package completion, and another on the other branch of your package to notify you that there were errors.

If you need to include more information, you should look into using CDO.


A wise man once said
"The only thing normal about database guys is their tables".

 

[lisa626]

I really have no clue about this, the person who did this left the company suddenly. I believe that we could use the 'SendMail Task', however how do I set up a profile on our server, we don't have Outlook or any mail programs on it, is there a way to set this up with SMTP????

 

[AlexCuse]

I'm not sure how SendMail actually sends the email, but I think it would work for you. Did you try it out?

If SendMail doesn't work, you could try something like this: (In an ActiveX script task)

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()

    Dim myMail   

    Set myMail=CreateObject("CDO.Message")

    myMail.Subject="New AgeIn Load"
    myMail.From="name@domain.com"
    myMail.To= "name@domain.com"
    myMail.TextBody="Enter Your Message Here, " & Chr(13) & Chr(13) _
	& "Make Sure to Delimit Lines" & Chr(13) & Chr(13) _
	& "Thanks," & Chr(13) & "<auto generated message>"
    myMail.Configuration.Fields.Item _
        ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")=2[/URL]
    'Name or IP of remote SMTP server
    myMail.Configuration.Fields.Item _
        ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] _
        =[b]ENTER IP ADDRESS TO SEND FROM HERE[/b]
    'Server port default = 25
    myMail.Configuration.Fields.Item _
        ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] _
        =25 
    myMail.Configuration.Fields.Update
    myMail.Send
    set myMail=nothing

If ErrorCount <> 0 Then
          Main = DTSTaskExecResult_Success
Else
          Main = DTSTaskExecResult_Failure
End If

End Function

Hope this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[lisa626]

The reason I wasn't sure if sendmail would work or not is it tell me to configure a MAPI mail profile.

 

[lisa626]

Ok, if I try the code you have above, what do I need to manually change to make this work???

Keeping in mind I know VERY little about programming/VB, trying to pick up where someone left off when they left unexpectedly.

 

[AlexCuse]

Ah, I see. That is something your DBA or Network Admin would probably be able to set up, but it's outside my knowledge. I think that the CDO/SMTP solution will work for you though. Let me know if that script works OK.

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[dr00bie]

Check out this link,

http://classicasp.aspfaq.com/email/how-do-i-send-e-mail-from-sql-server.html.

Drew

 

[AlexCuse]

Ok, if I try the code you have above, what do I need to manually change to make this work???

I never saw this, must've posted as I was typing my reply.

The only things you will need to change are myMail.Subject, To, From, TextBody.

And also you will need to enter your IP address where indicated.

Hope this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".