微软BI 之SSIS 系列 - 使用 Script Task 访问非 Windows 验证下的 SMTP 服务器发送邮件

"<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>"

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

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; 
        }