Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
Action_SendMail()
Dts.TaskResult = ScriptResults.Success
End Sub
Sub Action_SendMail()
Dim SB_Body As New System.Text.StringBuilder()
Dim isHTML As Boolean = ("HTML".ToUpperInvariant() = "HTML")
SendMail(SnippetParameter("User::EmailTo", "", "") _
, SnippetParameter("User::EmailFrom", "", "") _
, "" _
, "" _
, "Enterprise Datawarehouse ETL Process Halted" _
, SnippetParameter("User::EmailBodyText2", "", "") _
, False _
, SnippetParameter("User::SMTPServerName", "", "") _
, Convert.ToInt32(SnippetParameter("User::SMTPPort", "", "")) _
, Convert.ToBoolean(False) _
, SnippetParameter("User::EmailUserName", "", "") _
, SnippetParameter("User::EmailPassword", "", "") _
, SnippetParameter("User::Domain", "", "") _
, "High")
End Sub
Private Sub SendMail(ByVal sendTo As String, _
ByVal sentFrom As String, _
Optional ByVal cc As String = "", _
Optional ByVal bcc As String = "", _
Optional ByVal subject As String = "", _
Optional ByVal body As String = "", _
Optional ByVal isBodyHtml As Boolean = True, _
Optional ByVal smtpServer As String = "localhost", _
Optional ByVal smtpPort As Integer = 25, _
Optional ByVal useSSL As Boolean = False, _
Optional ByVal userName As String = "", _
Optional ByVal password As String = "", _
Optional ByVal domain As String = "", _
Optional ByVal attachments As String = "", _
Optional ByVal priority As String = "Normal")
Using oMessage As New System.Net.Mail.MailMessage()
oMessage.From = New System.Net.Mail.MailAddress(sentFrom)
If String.IsNullOrEmpty(sendTo) = False Then
oMessage.[To].Add(sendTo.Replace(";", ","))
End If
If String.IsNullOrEmpty(cc) = False Then
oMessage.CC.Add(cc.Replace(";", ","))
End If
If String.IsNullOrEmpty(bcc) = False Then
oMessage.Bcc.Add(bcc.Replace(";", ","))
End If
If priority.ToUpperInvariant() = "HIGH" Then
oMessage.Priority = System.Net.Mail.MailPriority.High
ElseIf priority.ToUpperInvariant() = "LOW" Then
oMessage.Priority = System.Net.Mail.MailPriority.Low
Else
oMessage.Priority = System.Net.Mail.MailPriority.Normal
End If
oMessage.Subject = subject
oMessage.Body = body
oMessage.IsBodyHtml = isBodyHtml
If Not String.IsNullOrEmpty(attachments) Then
Dim sFiles() As String = Split(attachments, "|")
For Each sFile As String In sFiles
If Not String.IsNullOrEmpty(sFile) AndAlso System.IO.File.Exists(sFile) Then
oMessage.Attachments.Add(New System.Net.Mail.Attachment(sFile))
End If
Next
End If
Dim mySmtpClient As New System.Net.Mail.SmtpClient(smtpServer, smtpPort)
If String.IsNullOrEmpty(userName) Then
mySmtpClient.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials
Else
mySmtpClient.Credentials = New System.Net.NetworkCredential(userName, password, domain)
End If
mySmtpClient.EnableSsl = useSSL
Try
mySmtpClient.Send(oMessage)
Finally
If oMessage IsNot Nothing AndAlso oMessage.Attachments IsNot Nothing AndAlso oMessage.Attachments.Count > 0 Then
For Each attachment As System.Net.Mail.Attachment In oMessage.Attachments
If attachment IsNot Nothing Then attachment.Dispose()
Next
oMessage.Attachments.Dispose()
End If
End Try
End Using
End Sub
#Region "Helper functions"
Private Property SnippetParameter(ByVal sVarName As String, ByVal sConnName As String, ByVal sConnPropName As String) As Object
Get
Dim oRetVal As Object
If String.IsNullOrEmpty(sConnName) = False Then
Dim oCon As ConnectionManager
oCon = Dts.Connections(sConnName)
If String.IsNullOrEmpty(sConnPropName) Then
sConnPropName = "ConnectionString"
ElseIf sConnPropName = "<object>" Then
Return oCon
End If
oRetVal = oCon.Properties(sConnPropName).GetValue(oCon)
ElseIf String.IsNullOrEmpty(sVarName) = False Then
oRetVal = ReadVariable(sVarName)
Else
oRetVal = Nothing
End If
Return oRetVal
End Get
Set(ByVal oValue As Object)
If String.IsNullOrEmpty(sConnName) = False Then
Dim oCon As ConnectionManager
oCon = Dts.Connections(sConnName)
If String.IsNullOrEmpty(sConnPropName) Then
sConnPropName = "ConnectionString"
End If
oCon.Properties(sConnPropName).SetValue(oCon, oValue)
ElseIf String.IsNullOrEmpty(sVarName) = False Then
WriteVariable(sVarName, oValue)
End If
End Set
End Property
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables = Nothing
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
If Not String.IsNullOrEmpty(varName) AndAlso Dts.VariableDispenser.Contains(varName) Then
Dim vars As Variables = Nothing
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Else
result = Nothing
End If
Catch ex As Exception
Throw ex
End Try
Return result
End Function
#End Region
End Class |
|
Properties
Expression: "Enterprise Datawarehouse ETL Process Halted
The ETL process detected that a previous incremental load did not completed successfully. The ETL logs and configuration settings must be reset before another incremental data load can take place.
Please refer to the document -ETL Restart Guidance- for detailed information on how to restart the ETL process.
The information required to make the reset is shown below:
Last ExecutionLogId: " + (DT_STR, 8, 1252)@[User::LastExecutionLogId]
+ "
Last Execution Start Date/Time: " + @[User::LastExecutionLogStartDtTm] + "
Last Execution End Date/Time: " + @[User::LastExecutionLogEndDtTm] + "
Last Execution Log Status: " + @[User::LastExecutionLogStatus]
