微軟BI 之SSIS 系列 - 使用 Script Task 訪問非 Windows 驗證下的 SMTP 伺服器發送郵件

標籤：style   blog   http   color   io   os   使用   ar   strong   

開篇介紹

大多數情況下我們的 SSIS 包都會配置在 SQL Agent Job 中周期性的按計劃執行，比如每天晚上調用 SSIS 封裝重新整理資料，處理 Cube 等。一旦 SSIS 包中出現任何異常，報錯，那麼配置在 SQL Agent Job 中的通知，寄件提醒就會把這些錯誤資訊發郵件到指定的使用者或者系統維護者，這樣就起到了一個錯誤監控的作用。

但是在有的情況下，有一些自訂的 SSIS 調度架構的計劃調度都不是通過 SQL Agent Job 配置來完成的。比如我以前在一個小項目中設計過一個 SSIS 調度架構，只有一個主包配置在 SQL Agent Job 中，所有子包的上線不由 SQL Agent Job 支配。每次新增的子包要上線，只需要將相應的資訊以及調度計劃註冊到相應的表中即可，不需要開啟 SQL Agent Job 調整各個子包的執行順序等等。那麼在這種情況下，就只能在 SSIS 內部使用發送郵件的功能來發送錯誤資訊，因為所有的子包和 SQL Agent Job 都是沒有直接關係的。

在 SSIS 中我們可以通過 Send Mail Task 或者 Script Task 來發送郵件，當然還有第三種方式就是寫預存程序調用發送郵件預存程序.

Send Mail Task 發送郵件

Send Mail Task 的使用其實非常的簡單，配置的步驟也並不多，但是在使用它的時候有幾個限制：

1. 只能發送普通的文字格式設定的郵件，也就是說不支援 HTML 格式的郵件。
2. 串連 SMTP Server 時不支援使用者填寫使用者名稱和密碼，也就是說使用者要麼在域環境下 SMTP Server 對使用者進行 Windows 方式驗證，要麼就是訪問的 SMTP Server 支援匿名訪問，不需要提供使用者名稱和密碼。
3. SMTP Server 的連接埠號碼使用預設的，也就是說如果在域中的 SMTP Server 連接埠號碼不是預設的話 Send Mail Task 就無法使用了。 

這裡是我常用的一個測試伺服器位址 http://www.yopmail.com/en/ ，免費的並支援匿名訪問，可以通過它來進行郵件發送的測試。關於 Send Mail Task 的使用方式就不在這裡示範了，在非域和非 Windows 驗證方式下用我提到的可匿名訪問的 Yop Mail 一試便知，主要是講 Script Task 發送郵件的方式。

Script Task 發送郵件

建立一個包並首先建立一個 SMTP 串連 - New Connection。

選擇 SMTP 連線管理員。

以我的郵箱為例子 [email protected] 發送郵箱伺服器是 smtp.126.com，這個可以在網易郵箱配置頁面查到。但是非常遺憾！在這裡仍然是沒有地方可以配置使用者名稱和密碼的，預設的情況下仍然是只支援 Windows 驗證或者匿名訪問。

所以說，如果想要在 SMTP Connection 裡自己動手配置使用者名稱和密碼是不可能的，那麼像這種 163,126等等很多很多郵箱直接通過 SMTP Connection 是肯定無法完成發送郵件操作的。

看時一定要理解一點：幾乎每一個 SSIS 控制項的屬性都是可配置的，也是可擷取的，這是我們在 SSIS ETL 的學習過程中解決很多很多問題的最關鍵的地方！

建立一些常用的變數，寄件者，收件者，抄送，郵件標題，內容，附件地址，還有使用者名稱和密碼等。

PS：關於這個密碼有一個故事，以前在公司裡有一個管機房管電腦的同事，他的網路下載速度最快也沒人管，他的電腦裡有各種遊戲，高清藍光和 1024。每次大傢伙要點東西像擠牙膏似的，大家都在想辦法搞他的密碼，直到後來大家有意無意記了並試出了密碼 - 大概就是 lowformat1MBD!! 類似於這樣的！看了密碼之後我們都一致認為這個密碼確實很符合他的職業，不過貌似苦大仇深啊！

其中 PV_CONTENT 變數描述的是郵件發送的 HTML 格式的內容，注意是字串類型的因此需要雙引號。

"<h1>Hi BIWORK!</h1><p>This is a test email from the test SSIS package of BIWORK!</p><p>Server Name - "+ @[System::MachineName] +"</p><p>Package Name - "+ @[System::PackageName] +"</p><p>Package Start Time - "+ (DT_WSTR, 12) ( DT_DBDATE) @[System::StartTime] +"</p><p></br>Thanks and Regards!</p><p>Simon</p>"

系統變數的資訊也通過運算式嵌入到郵件內文中。

編輯 Script Task 並引入這些變數，Script Task 中的代碼 -

using System.Net.Mail; // BIWORK Addedusing System.Text.RegularExpressions; // BIWORK Added

Main 中的程式碼封裝括處理優先順序，多個收件者的處理，使用者名稱和密碼，添加附件的方式等等。

public void Main()        {            // Default Priority and SMTP Server Port            int iPriority = 1;            int smtpPort = 25;                         //User::PV_ATTACHED_FILE,User::PV_CONTENT,User::PV_MAIL_FROM,            //User::PV_MAIL_TO,User::PV_MAIL_TO_CC,User::PV_TITLE            String mailFrom = Dts.Variables["User::PV_MAIL_FROM"].Value.ToString();            String mailTo = Dts.Variables["User::PV_MAIL_TO"].Value.ToString();            String mailToCC = Dts.Variables["User::PV_MAIL_TO_CC"].Value.ToString();            String mailTitle = Dts.Variables["User::PV_TITLE"].Value.ToString();            String mailContent = Dts.Variables["User::PV_CONTENT"].Value.ToString();            String mailAttachedFilePath = Dts.Variables["User::PV_ATTACHED_FILE"].Value.ToString();                        // User Information            String loginUser = Dts.Variables["User::PV_LOGIN_USER"].Value.ToString();            String loginPwd = Dts.Variables["User::PV_LOGIN_PWD"].Value.ToString();            // Get SMTP Server Information from Connection Manager            String smtpServer = Dts.Connections["CM_SMTP_126"].Properties["SmtpServer"].GetValue(Dts.Connections["CM_SMTP_126"]).ToString();            try            {                SmtpClient smtpClient = new SmtpClient();                MailMessage message = new MailMessage();                 MailAddress fromAddress = new MailAddress(mailFrom, "BIWORKTEST");                string[] sEmailTo = Regex.Split(mailTo, ";");                string[] sEmailToCC = Regex.Split(mailToCC, ";");                smtpClient.Host = smtpServer;                smtpClient.Port = smtpPort;                // Login information                System.Net.NetworkCredential myCredentials =                   new System.Net.NetworkCredential(loginUser, loginPwd);                smtpClient.Credentials = myCredentials;                // Attachment                 System.Net.Mail.Attachment attachment;                attachment = new System.Net.Mail.Attachment(mailAttachedFilePath);                message.Attachments.Add(attachment);                                 message.From = fromAddress;                  // Multiple email to address                if (sEmailTo != null)                {                    for (int i = 0; i < sEmailTo.Length; ++i)                    {                        if (sEmailTo[i] != null && sEmailTo[i] != "")                        {                            message.To.Add(sEmailTo[i]);                        }                    }                }                // Multiple cc address                if (sEmailToCC != null)                {                    for (int i = 0; i < sEmailToCC.Length; ++i)                    {                        if (sEmailToCC[i] != null && sEmailToCC[i] != "")                        {                            message.To.Add(sEmailToCC[i]);                        }                    }                }                // Email priority                switch (iPriority)                {                    case 1:                        message.Priority = MailPriority.High;                        break;                    case 3:                        message.Priority = MailPriority.Low;                        break;                    default:                        message.Priority = MailPriority.Normal;                        break;                }                message.Subject = mailTitle;                message.IsBodyHtml = true;                message.Body = mailContent;                smtpClient.Send(message);            }catch (Exception ex)            {                Dts.TaskResult = (int)ScriptResults.Failure;            }            // Close Script Task with success             Dts.TaskResult = (int)ScriptResults.Success;         }

儲存並執行 SSIS 包，執行成功！

優先順序別，標題，發送人等資訊 -

附件資訊和內容都可以看的到，包括在抄送欄中抄送給了自己，Package Start Time 的時間是來自虛機的時間，我虛機的時間還是 28日，這個是正確的 -

總結

從中可以看出，雖然在 SSIS 中提供了 SMTP 的串連方式，但是實際上它還是預設只允許通過 Windows 驗證或者匿名訪問的方式來訪問。但是我們通過 Script Task 在 Script 中關聯到了 SMTP 伺服器，並同時寫入使用者名稱和密碼，這樣就實現了在 SSIS 中訪問非域環境下第三方的 SMTP 伺服器並通過身分識別驗證且發送郵件的效果。

當然，這種方式在實際項目中可能用的並不是很多，因為項目中的郵件肯定是通過域驗證的方式去訪問更安全一些，畢竟系統資訊的傳遞只能限制在域中。

在項目中使用郵件通知如是 Windows 驗證的方式，有這幾個點需要注意：

1. 在開發與測試過程中，手動執行 SSIS Package 的這個使用者要有域中發送郵件的許可權，能夠通過 Windows 驗證。
2. 當包部署到 SQL Agent Job 中後，執行 SQL Agent Job 的帳號要有域發送郵件的許可權，也必須能通過 Windows 驗證。
3. 如果不需要部署到 SQL Agent Job，那麼就直接使用 Send Mail Task 通過 Windows 驗證也可以直接發送錯誤訊息提醒。

疑問

在這個例子中的代碼是不是一定要像這樣去訪問 SMTP Server 的串連資訊，感覺是不是多此一舉？ - 可以不用這樣訪問，其實很多 SMTP 的配置資訊最為方便的是配置成變數，通過變數傳遞給 Script Task 就可以了。這裡通過這樣的一種方式來表示在 Script Task 中如何擷取連線管理員的屬性資訊的，因為很多時候我們只知道如何去擷取變數資訊，這一點體現了 C# Script 的靈活性，我們在 Script 中可以做非常多的事情。包括這些訪問 SMTP Server 如何使用 smtpClient 發送郵件的代碼可以在網上找到現成的很多很多樣本，而我們要做的事情就是大膽的靈活運用它們來解決我們的問題。

在我的這些文章中也提到了郵件處理相關的內容

微軟BI 之SSRS 系列 - 報表郵件訂閱中 SMTP 伺服器匿名訪問與 Windows驗證, 以及如何成功訂閱報表的執行個體

更多 BI 文章請參看 BI 系列隨筆列表 (SSIS, SSRS, SSAS, MDX, SQL Server)  如果覺得這篇文章看了對您有協助，請協助推薦，以方便他人在 BIWORK 部落格推薦欄中快速看到這些文章。

微軟BI 之SSIS 系列 - 使用 Script Task 訪問非 Windows 驗證下的 SMTP 伺服器發送郵件