Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
Changing the DTS Send Mail Task
By Darren Green
Version 7.0/2000
Level Intermediate

This is a simple example of using an ActiveX Script Task to dynamically change the main properties of a DTS Send Mail task. First set the message subject to the same name as the package. The body of the message is a combination of a static string and a global variable value. Finally we read the filename from a connection and set this as an attachment. This would be useful if you wish to export a file and then email it to somebody.

' Pkg 235
Option Explicit

Function Main()

	Dim oPkg, oTasks, oSendMailTask

	' Get Package object
	Set oPkg = DTSGlobalVariables.Parent

	' Get Tasks collection
	Set oTasks = oPkg.Tasks

	' Get DTS Send Mail Task by Name
	Set oSendMailTask = oTasks("DTSTask_DTSSendMailTask_1").CustomTask

	' Set Subject to the Package Name
	oSendMailTask.Subject = oPkg.Name

	' Set Message Text, including a global variable value
	oSendMailTask.MessageText = "Dummy Text." & vbCrLf & _
		"MyGlobalVariable Value :" & DTSGlobalVariables("MyGlobalVariable").Value

	' Set the attachement to the file of the named connection
	oSendMailTask.FileAttachments = oPkg.Connections("Text File (Destination)").DataSource

	' Clean Up
	Set oSendMailTask = Nothing
	Set oTasks = Nothing
	Set oPkg = Nothing

	Main = DTSTaskExecResult_Success
End Function