SSIS中通过Script Task发送邮件 Sql2005
需求:
由于SSIS中的Send Mail Task不能很好自定义邮件的内容,通常都是以文本的方式表现.
现在有一个需求, 希望通过SSIS在处理过程中根据不同的执行结果.发送HTML格式的邮件.
很明显通过Send Mail Task不能达成要求. 那么我们通过Script Task自定义编程可实现.
实现过程:
在Script Task中的Script选项中点击Design Script进入VS编程环境中(VB.NET),
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Imports System.Data.OleDb
Public Class ScriptMain
Public Sub Main()
BodyDetail()
Dts.TaskResult = Dts.Results.Success
End Sub
''' <summary>
''' 发送邮件讯息
''' </summary>
'' ' <param name="from">寄件人地址</param>
'' ' <param name="recepient">收件人地址</param>
'' ' <param name="bcc">密件收件人</param>
'' ' <param name="cc">CC接受</param>
'' ' <param name="subject">主题讯息</param>
'' ' <param name="body">邮件讯息</param>
Public Shared Sub SendMailMessage(ByVal from As String, ByVal recepient As String, ByVal bcc As String, ByVal cc As String, ByVal subject As String, ByVal body As String)
' 初始化一个新的实例 MailMessage
Dim mMailMessage As New MailMessage()
mMailMessage.From = New MailAddress(from)
mMailMessage.To.Add(New MailAddress(recepient))
If Not bcc Is Nothing And bcc <> String.Empty Then
mMailMessage.Bcc.Add(New MailAddress(bcc))
End If
If Not cc Is Nothing And cc <> String.Empty Then
mMailMessage.CC.Add(New MailAddress(cc))
End If
mMailMessage.Subject = subject
mMailMessage.Body = body
mMailMessage.IsBodyHtml = True
mMailMessage.BodyEncoding = System.Text.Encoding.UTF8
mMailMessage.Priority = MailPriority.Normal
Dim mSmtpClient As New SmtpClient()
mSmtpClient.Host = "sjexchange"
mSmtpClient.DeliveryMethod = SmtpDeliveryMethod.Network
mSmtpClient.Send(mMailMessage)
End Sub
Public Shared Sub BodyDetail()
Dim objcon As New OleDbConnection("provider=sqloledb.1;initial catalog=database;data source=172.0.0.0;user id=sa;pwd=")
Dim dataview As New DataView
Dim ds As New DataSet
Dim ncopper As String
Dim sqlstr, body As String
'想出什么资料都行.
sqlstr = "select * from tempquote"
Dim objcmd As New OleDbDataAdapter(sqlstr, objcon)
objcmd.Fill(ds, "tab")
dataview = New DataView(ds.Tables("tab"))
If ds.Tables(0).Rows.Count <= 0 Then
Exit Sub
Else
'由于我只有一笔资料,故没有使用For 语句.
ncopper = Trim(CStr(dataview.Item(0).Item("ncopper")))
'只要把HTML包进来就行(你想要多复杂都可以)
body = " <body>"
body = body + "<table border='1' cellspacing='0' cellpadding='0' width='70%' style='border:none;' align='left'>"
body = body + " <tr>"
body = body + " <td> </td>"
body = body + " <td><label for='ncopper'> copper</label></td>"
body = body + " </tr>"
body = body + " <tr>"
body = body + " <td> new:</td>"
body = body + " <td><label for='ncopper'> " + ncopper + "</label></td>"
body = body + " </tr>"
body = body + "</table>"
body = body + "</body>"
End If
'邮件地址可以通过SSIS变量传递
SendMailMessage("XXXX@XXXX.om", "AAAA@XXXX.com", "BBBB@XXXX.com", "CCCC@XXXX.com", "CCL The 4-Raw-Materials Update", body)
End Sub
End Class
