Visual Studio 2010中SSIS Send Mail Task脚本故障求助

Hey there! Let's dig into your SSIS email script issue in Visual Studio 2010. First, let's clean up and format your partial code properly so we can spot potential issues easier:

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion
using System.Text.RegularExpressions;
using System.Net.Mail;
using System.IO;

namespace ST_dd466aa1cac943eb887bc0d48f753e68
{
    /// <summary>
    /// ScriptMain是脚本的入口类。请勿更改此类的名称、属性或父类。
    /// </summary>
    // 注意：这里应该有SSISScriptTaskEntryPointAttribute和继承VSTARTScriptObjectModelBase的代码
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        // 你的Main方法和其他逻辑应该在这里
    }
}

Since you didn't specify the exact error you're hitting, let's cover the most common pitfalls with SSIS script tasks for sending emails in VS2010, along with fixes and a working example:

Common Issues & Fixes #

• Missing References or Attributes: Your partial code is missing the required [SSISScriptTaskEntryPointAttribute] and proper inheritance. Also, ensure your script project references System.Net and System.IO (right-click References in the script editor → Add Reference to check).
• SMTP Configuration Mistakes: This is the #1 culprit. Double-check your SMTP server address, port (25, 587 for TLS, 465 for SSL), SSL/TLS settings, and authentication credentials. Many servers block anonymous access now.
• Invalid Email Addresses: If you're using that Regex to validate addresses, make sure your pattern correctly matches standard email formats (a simple pattern like ^[^@\s]+@[^@\s]+\.[^@\s]+$ works for most cases).
• Permission Problems: If running the package via SQL Server Agent, the agent service account needs permissions to access the SMTP server and any attachment files. Local runs use your user account, so test with that first.
• Uncaught Exceptions: Always wrap your email logic in a try-catch block to capture errors and fire SSIS events for debugging.

Full Working Example #

Here's a complete, tested script you can adapt to your needs. It uses SSIS variables for configuration (best practice instead of hardcoding):

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using System.Net.Mail;
using System.Net;
using System.IO;
#endregion

namespace ST_dd466aa1cac943eb887bc0d48f753e68
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            bool fireAgain = false;
            try
            {
                // Pull config from SSIS variables (create these in your package first)
                string smtpServer = Dts.Variables["User::SMTPServer"].Value.ToString();
                int smtpPort = Convert.ToInt32(Dts.Variables["User::SMTPPort"].Value);
                bool enableSSL = Convert.ToBoolean(Dts.Variables["User::EnableSSL"].Value);
                string senderEmail = Dts.Variables["User::SenderEmail"].Value.ToString();
                string senderPassword = Dts.Variables["User::SenderPassword"].Value.ToString();
                string recipientEmail = Dts.Variables["User::RecipientEmail"].Value.ToString();
                string subject = Dts.Variables["User::EmailSubject"].Value.ToString();
                string body = Dts.Variables["User::EmailBody"].Value.ToString();

                // Validate recipient email
                if (!IsValidEmail(recipientEmail))
                {
                    Dts.Events.FireError(0, "Invalid Email", $"Recipient address {recipientEmail} is not valid.", "", 0);
                    Dts.TaskResult = (int)DTSExecResult.Failure;
                    return;
                }

                using (MailMessage mail = new MailMessage(senderEmail, recipientEmail))
                {
                    mail.Subject = subject;
                    mail.Body = body;
                    mail.IsBodyHtml = false; // Set to true if using HTML content

                    // Add attachment (optional)
                    string attachmentPath = Dts.Variables["User::AttachmentPath"].Value.ToString();
                    if (!string.IsNullOrEmpty(attachmentPath) && File.Exists(attachmentPath))
                    {
                        mail.Attachments.Add(new Attachment(attachmentPath));
                    }
                    else if (!string.IsNullOrEmpty(attachmentPath))
                    {
                        Dts.Events.FireWarning(0, "Attachment Missing", $"File {attachmentPath} not found. Skipping attachment.", "", 0);
                    }

                    using (SmtpClient smtpClient = new SmtpClient(smtpServer, smtpPort))
                    {
                        smtpClient.EnableSsl = enableSSL;
                        smtpClient.Credentials = new NetworkCredential(senderEmail, senderPassword);
                        smtpClient.Send(mail);
                    }

                    Dts.Events.FireInformation(0, "Success", $"Email sent to {recipientEmail} successfully.", "", 0, ref fireAgain);
                    Dts.TaskResult = (int)DTSExecResult.Success;
                }
            }
            catch (Exception ex)
            {
                Dts.Events.FireError(0, "Email Failed", $"Error sending email: {ex.Message}\nStack Trace: {ex.StackTrace}", "", 0);
                Dts.TaskResult = (int)DTSExecResult.Failure;
            }
        }

        private bool IsValidEmail(string email)
        {
            // Basic email validation regex
            string emailPattern = @"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$";
            return Regex.IsMatch(email, emailPattern, RegexOptions.IgnoreCase);
        }
    }
}

Debugging Tips #

1. Add Breakpoints: In the script editor, click the gutter next to lines to set breakpoints, then run the package in debug mode to step through the code.
2. Use SSIS Events: The FireInformation, FireWarning, and FireError methods will output details to the SSIS Execution Results tab, which is invaluable for troubleshooting.
3. Test SMTP Separately: Use a tool like PowerShell or Telnet to test sending an email via your SMTP server outside of SSIS to rule out server-side issues.

内容的提问来源于stack exchange，提问作者Tunde