Email in DTS package

[thack111]

I am trying to build a DTS package that will import, minipulate and store data, then send out emails to let people know the data is there. I did this with a few querys, and som VBScript. In my script I create a outlook object, assign to:, body:, cc:, and send the email. This all works fine, BUT when I schedule the package I get access violation errors on the line that creates the outlook object. Here is a sample of how I am doing this:

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

Function Main()
Dim outlook
Dim mail

Set Outlook = CreateObject("outlook.Application&quot
Set mail = Outlook.CreateItem(0)
mail.Body = "hello again.....msg"
mail.Subject = "Subject!"
mail.To = "MyAddress@domain.com"
mail.Send
Main = DTSTaskExecResult_Success
End Function


I found some help on microsoft knowledge base, it said to add the user running the SQL Server Agent to the DCOM, I did. It didn't help. Any help would be greatly appreciated.

Thanks,
thack

 

[pabloj]

I think that the best (and most performant if you have to send many) would be a custom task to send email with SMTP (there are some free libraries around), and not via Outlook.
Unfortunately I've not found the time to code it myself.

Stick to your guns