'//------------------------------------------------------------------------------------------------
'// FILENAME : DTS.Scripts.ConfirmationEmail.vbs
'// AUTHOR : Charles Chen
'// PURPOSE : This script sends a confirmation email to the configured admin email
'// address using the configured server and port.
'//------------------------------------------------------------------------------------------------
Function Main()
'//----------------------------------------------------------------------------------------
'// SETTINGS. (G) DENOTES THAT THIS SETTING IS FROM A GLOBAL VARIABLE SET BY A DYNAMIC
'// PROPERTY TASK WHICH IS DRIVEN BY A .ini FILE. THE OTHER SETTINGS CAN ONLY BE
'// CONFIGURED IN THIS SCRIPT
'//----------------------------------------------------------------------------------------
'// (G) ADDRESS OF THE MAIL SERVER
dim s_MailServer : s_MailServer = CStr(DTSGlobalVariables("DTS.MailServerAddress"))
'// (G) THE PORT OF THE EMAIL SERVER
dim s_MailServerPort : s_MailServerPort = CStr(DTSGlobalVariables("DTS.MailServerPort"))
'// (G) THE RECIPIENT OF THE EMAIL
dim s_EmailAddress : s_EmailAddress = CStr(DTSGlobalVariables("DTS.AdminEmail"))
'// (G) THE EMAIL ADDRESS TO DISPLAY IN THE "FROM" FIELD OF THE MESSAGE
dim s_FromAddress : s_FromAddress = CStr(DTSGlobalVariables("DTS.EmailFromValue"))
'// (G) THE USERNAME TO USE WHEN LOGGING INTO THE MAIL SERVER
dim s_Username : s_UserName = CStr(DTSGlobalVariables("DTS.MailServerUsername"))
'// (G) THE PASSWORD TO USE WHEN LOGGING INTO THE MAIL SERVER
dim s_Password : s_Password = CStr(DTSGlobalVariables("DTS.MailServerPassword"))
'// (G) THE LOCATION OF THE LOG FILE
dim s_LogFile : s_LogFile = CStr(DTSGlobalVariables("DTS.LogFilePath"))
'// (G) THE SUBJECT OF THE EMAIL ADDRESS
dim s_EmailSubject : s_EmailSubject = "Data Import Message"
'//----------------------------------------------------------------------------------------
'// INITIALIZE MESSAGE STRING
'//----------------------------------------------------------------------------------------
dim globalVar
dim StrBuilder : set StrBuilder = new StringBuilder
StrBuilder.Append(" ")
StrBuilder.Append(" ")
StrBuilder.Append(" This is a confirmation email from the DTS package.")
StrBuilder.Append("
")
StrBuilder.AppendFormat " The package executed; this message was generated at: {0}
", FormatDateTime(Now, 0)
'//----------------------------------------------------------------------------------------
'// LIST GLOBAL VARIABLES (NAME+VALUE PAIR)
'//----------------------------------------------------------------------------------------
StrBuilder.Append("
")
StrBuilder.Append(" The following is a listing of Global Variables:
")
StrBuilder.Append(" ")
'// LOOP THROUGH THE GLOBAL VARIABLES AND LIST THEIR NAME AND VALUE
for each globalVar in DTSGlobalVariables
if InStr(LCase(CStr(globalVar.Name)), "password") > 0 then
StrBuilder.AppendFormat "- {0} = {1}
", Array(CStr(globalVar.Name), "**********")
else
StrBuilder.AppendFormat "- {0} = {1}
", Array(CStr(globalVar.Name), CStr(globalVar.Value))
end if
next
'//----------------------------------------------------------------------------------------
'// END HTML MESSAGE STRING
'//----------------------------------------------------------------------------------------
StrBuilder.Append("
")
StrBuilder.Append("
")
StrBuilder.AppendFormat "A detailed log file can be found at {0}", s_LogFile
StrBuilder.Append("
")
StrBuilder.Append( "[End of Message]")
'//----------------------------------------------------------------------------------------
'// INITIALIZE MESSAGE EMAIL
'//----------------------------------------------------------------------------------------
dim Message
set Message = CreateObject("CDO.Message")
Message.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
Message.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = s_MailServer
Message.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = s_MailServerPort
Message.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = s_Username
Message.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = s_Password
Message.Configuration.Fields.Update
Message.Subject = s_EmailSubject
Message.From = s_FromAddress
Message.Sender = s_FromAddress
Message.ReplyTo = s_FromAddress
Message.To = s_EmailAddress
Message.HtmlBody = StrBuilder.ToString()
Message.Send
'//----------------------------------------------------------------------------------------
'// CLEAN UP
'//----------------------------------------------------------------------------------------
set Message = nothing
Main = DTSTaskExecResult_Success
End Function
'//------------------------------------------------------------------------------------------------
'// STRING BUILDER CLASS FOR EFFICIENT CONCATENATION OF LARGE STRINGS; MUCH MORE EFFICIENT (IF
'// USING ONLY Append()) FOR MEDIUM TO LARGE STRINGS. BEST USAGE IS TO INSTANTIATE AT THE
'// BEGINNING OF EACH FILE AND USE IT TO BUILD OUTPUT
'//------------------------------------------------------------------------------------------------
class StringBuilder
dim index '// PLACEHOLDER IN THE ARRAY
dim ub '// UPPERBOUND
dim ar() '// OUR ARRAY BUFFER
private sub Class_Initialize()
Init()
end sub
private sub Class_Terminate()
erase ar
end sub
private sub Init()
erase ar
redim ar(20)
index = 0
ub = 10
end sub
'//--------------------------------------------------------------------------------------------
'// SUB TO ADD A STRING TO THE INTERNAL ARRAY
'// public default sub Append([str])
'// str : STRING TO APPEND TO THE INTERNAL STORAGE ARRAY
'//--------------------------------------------------------------------------------------------
public default sub Append(str)
ar(index) = str
index = index + 1
if index > ub then
ub = ub + 20
redim preserve ar(ub)
end if
end sub
'//--------------------------------------------------------------------------------------------
'// FUNCTION TO REPLACE STRING PLACEHOLDERS AND APPEND TO THE BUFFER
'// public sub AppendFormat([str], [tokens])
'// str : STRING TO APPEND (CONTAINING FORMATTING TOKENS)
'// tokens : FORMATTING VALUES
'//--------------------------------------------------------------------------------------------
public sub AppendFormat(str, tokens)
dim token
dim vtype
vtype = CInt(VarType(tokens))
if vtype < 6 or vtype = 8 then
'// IF THE SECOND ARGUMENT IS OF TYPE STRING, THEN SIMPLY REPLACE THE VALUE
str = Replace(str, "{0}", CStr(tokens))
else
'// LOOP THROUGH THE SECOND ARGUMENT (SHOULD BE ARRAY - VARIANT, VARTYPE = 8204)
dim i
for i = LBound(tokens) to UBound(tokens)
str = Replace(str, "{" & i & "}", tokens(i))
next
end if
Append(str)
end sub
'//--------------------------------------------------------------------------------------------
'// FUNCTION TO OUTPUT THE ARRAY AS A STRING
'//--------------------------------------------------------------------------------------------
public function ToString()
redim preserve ar(index - 1)
ToString = Join(ar, "")
Init()
end function
end class
'/////////////////////////////////////////////////////////////////////////////////////////////////
'// SAMPLE USAGE BELOW
'/////////////////////////////////////////////////////////////////////////////////////////////////
'dim StrBuilder
'set StrBuilder = new StringBuilder
'StrBuilder.AppendFormat "Hello {0} my name is {1}.", Array("Charles", "Julie")
'StrBuilder.AppendFormat " This is my dog {0}", "Henry"
'Response.Write StrBuilder.ToString()
'StrBuilder.Append("
THIS IS A NEW STRING")
'Response.Write StrBuilder.ToString()