I am very new to DTS and ActiveX scripts. I had a package that was running
without incident until recently. I honestly don't know if Network Operations
changed anything on the server. When executing the below ActiveX script via
DTS, I receive the following error message:

Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Object required: 'RS'

Error on Line 42

Here is my ActiveX script in its entirety:
Option Explicit

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

Function Main()

Dim ObjPackage
Dim ObjData
Dim sAttachmentList
Dim RS
Dim oStep
Dim oPackage
Dim Mail
Dim sAddressList
Dim sAddressArray
Dim sAddressLoop
Dim sAttachmentArray
Dim sAttachment
Dim ErrorCount
Dim sFromName

' Set the Package Object Reference
Set oPackage = DTSGlobalVariables.Parent

'Create an ADO recordset
Set RS = CreateObject("ADODB.Recordset")

'Set the RecordSet to the value of the gvEmailList global
'var set in the previous DTS Step
Set RS = DTSGlobalVariables("gvEmailList").Value

'Create a MailSender Object
Set Mail = CreateObject("Persits.MailSender")

'Set the SMTP Host
Mail.Host = "mailrelay.company.net"

ErrorCount = 0

'Each Item in the resultset is an Email to be sent. Loop through the
result set and send the emails.
DO WHILE NOT RS.EOF

IF ErrorCount > 10 THEN
EXIT DO
END IF

'Set some globals for use in post processing
DTSGlobalVariables("gvScheduledEmailId").Value =
RS.Fields("ScheduledEmailId").value
DTSGlobalVariables("gvScheduledEmailTypeId").Value =
RS.Fields("ScheduledEmailTypeId").value

'Parse the Address List and add an addres for each recipient
sAddressList = RS.Fields("ToAddress").Value
sAddressArray = Split(sAddressList,";")
FOR EACH sAddressLoop IN sAddressArray
Mail.AddAddress sAddressLoop
NEXT

'Set parameters for the email

Mail.From = RS.Fields("FromAddress").Value

'msgbox RS.Fields("FromAddress").Value

sFromName=Trim(RS.Fields("FromName") & "")
IF Len(sFromName) > 0 Then
Mail.FromName = RS.Fields("FromName").Value
END If

Mail.Subject = RS.Fields("Subject").Value
Mail.Body = RS.Fields("Body").Value

' Grab the attachment List
sAttachmentList = Trim(RS.Fields("AttachmentList") & "")

If Len(sAttachmentList) > 0 Then
sAttachmentArray = Split(sAttachmentList,",")
FOR EACH sAttachment IN sAttachmentArray
Mail.AddAttachment sAttachment
NEXT
End If

'Now Send the Email. Check for errors and if it succeeds, execute
the appropriate task
ON ERROR RESUME NEXT
Mail.Send
IF Err.Number <> 0 Then 'Error occured
ErrorCount = ErrorCount + 1
DTSGlobalVariables("gvErrorSource").Value = Err.Source
DTSGlobalVariables("gvErrorNumber").Value = Err.Number
DTSGlobalVariables("gvErrorDescription").Value = Err.Description
Set oStep = oPackage.Steps("DTSStep_DTSExecuteSQLTask_4")
oStep.Execute
Set oStep = oPackage.Steps("DTSStep_DTSActiveScriptTask_2")
oStep.Execute

ELSE
Set oStep = oPackage.Steps("DTSStep_DTSExecuteSQLTask_5")
oStep.Execute
Set oStep = oPackage.Steps("DTSStep_DTSExecuteSQLTask_2")
oStep.Execute
END IF

RS.MoveNext

'Reset the Mail Object
Mail.Reset

LOOP

Set Mail = Nothing
Set RS = nothing

Main = DTSTaskExecResult_Success

End Function

**********************
I believe it is failing at this line: DO WHILE NOT RS.EOF
I tried putting message boxes before this line, but receive the same error
anyway, just a different line number. Can anyone please shed some light? I
don't even know where to begin.

Thank you.

Looking at your CREATEOBJECT codes I would have thought this one is the problem

'Create a MailSender Object
Set Mail = CreateObject("Persits.MailSender")

Have they removed it?
Do you need to reregister the dll?



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Crazy about SQL Server" <CrazyaboutSQLServer (AT) discussions (DOT) microsoft.com> wrote