SQL Server Performance Forum – Threads Archive

DTS – SEND Mail

Please help. I’m trying to send email message only if error in previous activex script.
If I use the send mail object there are no properties to set to only send if…
So I’m trying to do this through activex. I found code on sqldts.com that helps, but having trouble with the dtsglobalvariables.parent. So, I have 3 tasks connected by workflow. THe first task is basically checking for error condition if it does exist, I want to send email, if not skip email and go to third task. I’ve got the skipping logic if I can use activex in task2 – but I need to initiate the send mail from the same active x(task2). Here’s the code to initiate email, but it is not working. I have not yet included the skipping logic in task2, but I have it. My problem is with the DTSGlobalVariables.Parent – how can I set this? I’m ok to hardcode the email info. ‘**********************************************************************
‘ Visual Basic ActiveX Script
‘************************************************************************
Function Main() Dim oPkg, oTasks, oSendMailTask ‘ Get Package object
Set oPkg = DTSGlobalVariables.Parent ‘ Get Tasks collection
Set oTasks = oPkg.Tasks.New("DTSSendMailTask")
Set oSendMailTask = oTasks.CustomTask ‘ Set To line
oSendMailTask.ToLine = "[email protected]" ‘ Set Subject to the Package Name
oSendMailTask.Subject = oPkg.Name ‘ Set Message Text, including a global variable value
oSendMailTask.MessageText = "Error in MCPE Pricing upload. Column header did not match expected values. " & vbCrLf & _
"MyGlobalVariable Value :" & DTSGlobalVariables("MyGlobalVariable").Value ‘ Clean Up
Set oSendMailTask = Nothing
Set oTasks = Nothing
Set oPkg = Nothing Main = DTSTaskExecResult_Success
End Function

Are you creating a new send task or is already in the parent dts? if it is new, you may have to save it first before you can access it in other statement. May the Almighty God bless us all!
www.empoweredinformation.com

Make sure you’re using proper privileged domain account, because the sendmail task in DTS tries to use the logged in user as the mapi profile. If you just want to send some simple email, I would recommend using xp_sendmail instead of the mail task–the mail task has too many limitations.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9283 fyi. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>