Including rowcount in sendmail task? 1

[AlexCuse]

Hi All,

I have a DTS package that takes information off our server and places it in an access database at a remote location so that it can be used for mailings. I am currently running it through a vb front end, and passing in global variables to specify what to export.

When this task is finished, I have it sending an email notification to all parties who need to know. My question is, how can I capture the rowcount from DTS task, and include this count in my email?

I imagine there is an ActiveX script to modify contents of the email, but how can I capture the rowcount and use this in that statement?

Any advice is greatly appreciated.

Thanks,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[unclerico]

Create a new global variable
Set it's type to <other>
Go on the Output Variable tab
Select RowSet
Select the new variable you just created from the dropdown
In an ActiveX Script do this:

Function Main()
        Dim oResults
        Dim bMail

        'set the oResults variable to retrieve the rowset
        Set oResults = DTSGlobalVariables("variablename").Value

        'call the SendMail function and pass it the RecordCount of the resultset
        bMail = SendMail(oResults.RecordCount)

        'if the message was sent successfully then return success else return failure
        If bMail = True Then
          Main = DTSTaskExecResult_Success
        Else
          Main = DTSTaskExecResult_Failure
        End If

End Function

'Purpose: To send an e-mail message
'Inputs: The RecordCount value of the result set
'Ouputs: boolean value
Function SendMail(iRecCount)
        Dim objMessage
        On Error Resume Next

	Dim myMail
        Dim iCount

        [b]iCount = iRecCount[/b]

	Set myMail=CreateObject("CDO.Message")

	myMail.Subject="your subject line"
	myMail.From="someone@copany.com"
	myMail.To="person1@company.com;person2@company.com"
	[b]myMail.TextBody="There were " & iCount & " records involved"[/b]
	myMail.Configuration.Fields.Item _
		("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")=2[/URL]
	'Name or IP of remote SMTP server
	myMail.Configuration.Fields.Item _
		("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] _
		="smtpservername"
	'Server port
	myMail.Configuration.Fields.Item _
		("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] _
		=25 
	myMail.Configuration.Fields.Update
	myMail.Send
	set myMail=nothing
 
        If Err.Count <> 0 Then
          SendMail = False
        Else
          SendMail = True
        End If

End Function

hopefully it works for you

 

[AlexCuse]

Hi UncleRico,

I had started thinking it would be a vb solution. I should have tried that rather than trying to modify the send mail task. This is great because it let me include another global variable in the email body for what month's data it is. Thanks for the code, it cut down on my time to get it running. I did have to change Err.Count to ErrorCount of course (at least it was near the bottom so it stuck out the first time I got the error message).

Also, I used to be able to throw a football a quarter mile too ;-)

Have a

, and a great weekend!

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.