SSIS Script Task Email with SqlConnection Logging / VB.Net

On a earlier post I talked about using a Foreach loop container to send dynamic emails. I finally was able to wrap up the project and decided to take it a little bit more by adding a logging method in the script task. Below is my code sample for getting values from ForEach Loop Container, creating the email and logging the sent email into sql server via a SqlConnection.

Sample Code:

Public Sub Main()
Dim EmailTypeID As Integer
Dim EmployeeID As String
Dim SalesID As String
Dim SamAccount As String
Dim RehireDate As String
Dim ServiceDate As String
Dim FromAddress As String
Dim ToAddress As String
Dim Subject As String
Dim Body As String
Dim Name As String

Try
' Log properties
EmailTypeID = Dts.Variables("User::EmailTypeID").Value
EmployeeID = Dts.Variables("User::EmployeeID").Value.ToString
SalesID = Dts.Variables("User::SalesID").Value.ToString
SamAccount = Dts.Variables("User::SAMAccount").Value.ToString
RehireDate = Dts.Variables("User::RehireDate").Value.ToString
ServiceDate = Dts.Variables("User::ServiceDate").Value.ToString

' Email properties
FromAddress = Dts.Variables("User::MailFromAddress").Value.ToString
ToAddress = Dts.Variables("User::MailToAddress").Value.ToString
Subject = Dts.Variables("User::MailSubject").Value.ToString
Body = Dts.Variables("User::MailBody").Value.ToString
Name = Dts.Variables("User::MailName").Value.ToString

If SendMail(FromAddress, ToAddress, Subject, Body, Name) = True Then
LogEmail(EmailTypeID, EmployeeID, SalesID, SamAccount, ToAddress, RehireDate, ServiceDate)
Dts.TaskResult = ScriptResults.Success
End If

Catch ex As Exception
ErrorEmail(ex)
Dts.TaskResult = ScriptResults.Failure
End Try
End Sub

Public Function SendMail(ByVal FromAddress As String, ByVal ToAddress As String, ByVal Subject As String, ByVal Body As String, ByVal Name As String) As Boolean
Dim myHtmlMessage As MailMessage
Dim mySmtpClient As SmtpClient
Dim Body2 As String
Dim NewHireUrl As String

'MsgBox("Send, started")
NewHireUrl = "http://msn.com"

Body2 = "Dear: " & Name & _
vbNewLine & vbNewLine & _
" message "

myHtmlMessage = New MailMessage(FromAddress, ToAddress, Subject, Body2)
mySmtpClient = New SmtpClient("smtp.server.com")
mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
mySmtpClient.Send(myHtmlMessage)
'MsgBox("Send, completed")
Return True

End Function

Public Function LogEmail(ByVal EmailTypeID As Integer, ByVal EmployeeID As String, ByVal SalesID As String, ByVal SamAccount As String, ByVal ToAddress As String, ByVal RehireDate As String, ByVal ServiceDate As String) As Boolean
Dim oCnn As New Data.SqlClient.SqlConnection
Dim sSQL As String
oCnn.ConnectionString = "Server=xxxx;Database=xxxx;User ID=user;Password=pass;Trusted_Connection=False"
sSQL = "insert into dbo.EmployeeEmail (DimEmailTypeKey, EmployeeID, SalesID, SamAccount, EmailAddress, DateOfEmail, RehireDate, ServiceDate, AuditKey) "
sSQL += "Values (@EmailTypeID, @EmployeeID, @SalesID, @SamAccount, @EmaillAddress, @DateOfEmail, @RehireDate, @ServiceDate, @AuditKey) "
Dim oCmd As SqlCommand = New SqlCommand(sSQL, oCnn)
oCmd.Parameters.Add("@EmailTypeID", SqlDbType.Int).Value = EmailTypeID
oCmd.Parameters.Add("@EmployeeID", SqlDbType.VarChar).Value = EmployeeID
oCmd.Parameters.Add("@SalesID", SqlDbType.VarChar).Value = SalesID
oCmd.Parameters.Add("@SamAccount", SqlDbType.VarChar).Value = SamAccount
oCmd.Parameters.Add("@EmaillAddress", SqlDbType.VarChar).Value = ToAddress
oCmd.Parameters.Add("@DateOfEmail", SqlDbType.VarChar).Value = Date.Now.ToString()
oCmd.Parameters.Add("@RehireDate", SqlDbType.VarChar).Value = RehireDate
oCmd.Parameters.Add("@ServiceDate", SqlDbType.VarChar).Value = ServiceDate
oCmd.Parameters.Add("@AuditKey", SqlDbType.VarChar).Value = 1

oCmd.CommandType = CommandType.Text
oCmd.Connection = oCnn
oCnn.Open()
oCmd.ExecuteNonQuery()
oCnn.Close()
Return False

End Function

Public Sub ErrorEmail(ByVal Exception As Exception)
Dim myHtmlMessage As MailMessage
Dim mySmtpClient As SmtpClient

myHtmlMessage = New MailMessage("admin@email.com", "admin@email.com", "Email Error", Exception.ToString())
mySmtpClient = New SmtpClient("smtp.server.com")
mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
mySmtpClient.Send(myHtmlMessage)
End Sub