SSIS中通过Script Task发送邮件 Sql2005

来源：互联网发布：如何在硬盘安装ubuntu 编辑：程序博客网时间：2024/05/18 20:08

需求:
    由于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