Multible recordsets in DTS Package

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

Import/Export (DTS) and Replication (2000)

Multible recordsets in DTS Package

Author

Topic

brownd92
Starting Member

8 Posts

Posted - 2005-03-10 : 09:21:27

Hi there,
Can you tell me if this is possible and how I would go about it?

In a DTS package i need to:

1. Bring back a recordset containing email addresses (recordset1)
2. Make a second query on the database with the email address as a parameter (in the where clause - recordset2)
3. write all the records in recordset2 to a html file and send it to the email address
4. keep looping until the end of recordset1

Make any sense?

I can already generate the emails but i cant get the second recordset to loop inside the first.
Here is my activex code:

######################################################
Option Explicit
On Error Resume Next
Function Main()
Dim strEmailBody
Dim iMsg
Dim oRS
Dim objMail
Dim oRsC
Set oRs=DTSGlobalVariables("RSEmails").Value
While Not oRS.EOF
Set iMsg = CreateObject("CDO.Message")
Set objMail = CreateObject("CDO.Message")

' Move to the next row in preparation for loop iteration
'Create the body of the email
strEmailBody = "Good morning," & vbCrLf & _
"You have outdated content to update " & _
" Please click on this link to rectify " & _
"http://www.whatever.com."
Set oRsC = DTSGlobalVariables("RSContractors").Value

While Not oRsC.EOF
strEmailBody = oRsC.Fields(2).Value & " " & oRsC.Fields(1).Value & vbCrLf
oRsC.MoveNext
wend
Set oRsC = Nothing
'
'Attach the file to an email and send it
objMail.From = " youremail@email.com"
objMail.To = oRS.Fields(4).Value
objMail.Subject = "Content needing attention"
objMail.TextBody = strEmailBody
objMail.AddAttachment("c:\report.html")
objMail.Send
Set objMail = nothing
oRS.MoveNext
wend
Set oRS = Nothing
Set oRsC = Nothing
Main = DTSTaskExecResult_Success
End Function
###################################################

Thanks

David

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources