Microsoft BI SSIS Series-Send mail using Script Task to access SMTP server under non-Windows authentication

Original: Microsoft BI SSIS series-Send mail using Script Task to access SMTP server under non-Windows authentication

Introduction to the outset

In most cases, our SSIS packages are configured to perform periodically on a scheduled basis in the SQL Agent Job, such as calling SSIS packages to refresh data every night, processing cubes, and so on. In the event of any exception in the SSIS package and an error, the notification is configured in the SQL Agent Job, which sends the error message to the specified user or system maintainer, which plays a role in error monitoring.

However, in some cases, the scheduled scheduling of some custom SSIS scheduling frameworks is not done through the SQL Agent Job configuration. For example, I used to design an SSIS scheduling framework in a small project, with only one master package configured in the SQL Agent job, and all child packages are not run by the SQL Agent job. Each new sub-package to go live, only need to register the corresponding information and scheduling plan to the appropriate table, you do not need to open the SQL Agent Job to adjust the order of the individual sub-packages and so on. In this case, it is only possible to use the ability to send messages inside SSIS to send error messages because all of the child packages are not directly related to the SQL Agent Job.

In SSIS, we can send mail through the Send Mail task or Script task, and, of course, the third way is to write the stored procedure call to send the message stored procedure.

Send mail Task sends a message

The use of the Send Mail Task is actually very simple and there are not many steps to configure, but there are several limitations when using it:

1. You can only send messages in plain text format, that is, messages in HTML format are not supported.
2. When you connect to an SMTP server, users are not supported to fill in the user name and password, which means that the user is either Windows-authenticated to the user in a domain environment or the SMTP server that is accessed supports anonymous access and does not require a user name and password.
3. The port number of the SMTP server uses the default, which means that the Send Mail Task cannot be used if the SMTP server port number in the domain is not the default.

Here is my usual test server address http://www.yopmail.com/en/, which is free and supports anonymous access, which can be used to test mail delivery. The use of the Send Mail task is not demonstrated here, in the non-domain and non-Windows authentication mode with the anonymous access I mentioned YOP Mail to try to know, mainly about the Script Task to send mail.

Script Task Send Message

Create a new package and start by creating an SMTP connection-new Connection.

Select the SMTP connection Manager.

Take my mailbox as an example [email protected] Send Mailbox server is smtp.126.com, this can be found in the NetEase mailbox Configuration page. But very sorry! There is still no place to configure the user name and password, and the default is still to support only Windows authentication or anonymous access.

So, if you want to set up your own user name and password in the SMTP Connection is not possible, then like this 163,126 and so many many mailboxes directly through the SMTP Connection is definitely unable to complete the Send mail operation.

It's important to understand that almost every SSIS control's properties are configurable and available , which is the key to solving many problems in the learning process of SSIS ETL!

Create a few common variables, sender, recipient, CC, message header, content, attachment address, user name and password, etc.

PS: There is a story about this password, before in the company has a tube machine housing computer colleagues, his network download speed is the fastest also no one tube, his computer has a variety of games, HD Blu-ray and 1024. Every time a big thing like a squeeze toothpaste, everyone is trying to get his password, until later people accidentally and unintentionally remember and try out the password-probably is lowformat1mbd!! similar to this! After reading the password we all agreed that the password really fits his profession, but looks like ferocious ah!

Where the pv_content variable describes the contents of the HTML format sent by the message, note that the string type requires double quotation marks.

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

Information about system variables is also embedded in the message body through expressions.

Edit the Script task and introduce these variables, the code in the Script task-

using // biwork Added using // biwork Added

The code in Main includes processing priority, processing of multiple recipients, user name and password, how attachments are added, and so on.

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

Save and execute the SSIS package and execute successfully!

Priority level, title, sender and other information-

Attachment information and content can be seen, including in the CC column to copy to yourself, package Start time is from the virtual machine time, my virtual machine time or 28th, this is correct-

Summary

As you can see, although the way SMTP is connected is provided in SSIS, it is in fact only allowed to be accessed through Windows authentication or anonymous access by default. However, we associate the SMTP server in script with the script Task and write the user name and password at the same time, thus implementing the effect of accessing the third-party SMTP server in SSIS and authenticating and sending the message in a non-domain environment.

Of course, this approach may not be much of a practical project, because messages in a project must be secured by domain authentication, after all, the delivery of system information can only be restricted to the domain.

There are several points to note when using mail notifications in your project as Windows authentication:

1. During the development and testing process, the user who executes the SSIS package manually has permission to send mail in the domain and is able to authenticate through Windows.
2. When a package is deployed to a SQL Agent job, the account that executes the SQL Agent job has permission to send mail to the domain, and must be able to authenticate through Windows.
3. If you do not need to deploy to the SQL Agent Job, you can use the Send Mail Task directly through Windows authentication or send an error message alert directly.

Questions

Does the code in this example have to be like this to access the SMTP Server connection information, feeling that it is not superfluous? -Can not be accessed, in fact, a lot of SMTP configuration information is the most convenient configuration variables, passed through the variable to the Script Task can be. Here's a way to show how to get the property information of the connection manager in the Script Task, because many times we only know how to get the variable information, which shows the flexibility of C # script and we can do a lot of things in script. Including the code that accesses how SMTP Server uses SmtpClient to send mail can find many, many examples on the web, and the only thing we need to do is to use them boldly to solve our problems.

in my articles, I also mentioned the message processing related content

Microsoft BI SSRS Series-SMTP Server anonymous access in report mail subscription with Windows Authentication, and how to successfully subscribe to an instance of a report

For more bi articles, see the Bi-series essay list (SSIS, SSRS, SSAS, MDX, SQL Server) If you feel this article has helped you, please help recommend it to make it easy for others to see these articles quickly in the Biwork blog recommendations Bar.

Microsoft BI SSIS Series-Send mail using Script Task to access SMTP server under non-Windows authentication