SendMailTask, DTS VB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SendMailTask, DTS VB

Author

Topic

brettk
Starting Member

2 Posts

Posted - 2003-04-04 : 08:36:00

I am trying to get a DTS Package to query the database and then send a separate email to the all of the returned email addresses.

I can get it to send 1 email but it then hangs before it sends the other 3, I think that I have something out of order, any ideas?

I don't want all the email addresses in 1 email and I am trying to avoid creating a second package and calling that.

Function Main()
dim myConn, myRecordset, mySQLCmdText
dim myConn2, myRecordset2, mySQLCmdText2
dim countr, iRowCount
Dim objStep, objTask, objSendMail

set myConn = CreateObject("ADODB.Connection")
set myRecordset = CreateObject("ADODB.Recordset")
set myRecordset2 = CreateObject("ADODB.Recordset")

myConn.Open = "Provider=SQLOLEDB.1;Data Source=(Local);Initial Catalog=WEB;user id = 'sa';password=''"

mySQLCmdText = "Select * from WEB..email where email_id < 5"
mySQLCmdText2 = "Select 'rowcount' = Count(*) from WEB..email where email_id < 5"

myRecordset.Open mySQLCmdText, myConn
myRecordset2.Open mySQLCmdText2, myConn

set Flds = myRecordset2.Fields
set iRowCount = Flds("rowcount")

If iRowCount.Value < 1 then
MsgBox " There are no records found."
Main = DTSTaskExecResult_Failure

Else
for countr = 1 to iRowCount
Set objPackage = DTSGlobalVariables.Parent
Set objStep = objPackage.Steps.New
Set objTask = objPackage.Tasks.New("DTSSendMailTask")
Set objSendMail = objTask.CustomTask
With objSendMail
.Name = "TestMail"
.Profile = "MS Exchange Settings"
.ToLine = myRecordset.Fields("Email_address").value
.Subject = myRecordset.Fields("Email_id").value
.MessageText = "This is a test "
.IsNTService = True
.SaveMailInSentItemsFolder = True
End With

objStep.TaskName = objSendMail.Name
objStep.Name = "TestMailStep"
objPackage.Steps.Add objStep
objPackage.Tasks.Add objTask

objPackage.Execute
Set objStep = Nothing
Set objSendMail = Nothing
Set oPackage = Nothing
Set oTask = Nothing
Next

End If

myRecordset.Close
myRecordset2.Close
myConn.Close

Main = DTSTaskExecResult_Success
End Function

Brett

Edited by - brettk on 04/04/2003 10:38:42

Edited by - brettk on 04/04/2003 10:43:23

dogli
Starting Member

32 Posts

Posted - 2003-04-11 : 18:43:55

I used ASPEmail component to send e-mail from DTS. It works very well. But you must have the ASPEmail component installed at your SQL SErver box. It's free for download at www.aspemail.com.

I don't know much about sending e-mail with MS Exchange server, so I can not provide your much more information on your package.

Hope this help.

Dogli

kumarnarain
Starting Member

3 Posts

Posted - 2003-04-16 : 08:55:29

I think it will be even more easier to configure SQL mail and then queue it up inside.The xp_sendmail can the be used to send mails.(stored procedure)There are a number of articles in this site which will show you how.I am just a beginner but solved the problem with the info from this site.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources