需求:
由于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 |
SSIS中通过Script Task发送邮件 Sql2005
最新推荐文章于 2021-04-13 17:04:32 发布