Automate Email Sending Using SSIS Script Task

As we know, SSIS offers all the necessary tasks to perform ETL operations. However, there are a few cases where its built-in tasks or transformations may not be helpful; hence, SSIS Script Task utilizes Microsoft Visual Studio Tools for Applications (VSTA) service as it provides a development environment where we can write VB or C# code to automate several processes like email sending. SSIS Script Task comes with special functionality that allows developers to integrate custom code into their ETL process or package.

Now, let's create an SSIS Script Task to achieve email-sending automation. We can select SSIS Script Task from the SSIS toolbox and drag and drop it on package SSIS_ScriptTask within the control flow. You can see this in the below screenshot.

Now, let's create variables. To open a variable instance, we need to right-click on the control flow.

The following variables can be created for the SSIS Script Task.

1. Email_Body (Mention the message you want to include in the email body)
2. Email_From (Mention the email ID from which you want to send the email)
3. Email_From_Pwd (Mention the password of the email ID from which you want to send the email)
4. Email_Subject (Mention the subject of the email)
5. Email_To (Mention the email ID to whom you want to send the email)

Below is the screen showing the variable created.

Now, let’s double-click on the SSIS Script task to open the editor and configure the component.

In the editor on the Script tab, specify ScriptLanguage as Microsoft Visual C# 2022, specify the method name as Main on EntryPoint, and specify ReadOnlyVariables, where we will select previously created variables by clicking the eclipse (...) button.

In the editor on the General tab, specify Name as ScriptTask in SSIS, specify Description as ScriptTask, and click ok to proceed.

Once we have selected previously created variables, we will click on Edit Script to add C# code.

Once we click on Edit Script, the ScriptMain.cs class file will open below. We need to add the references below to send an email from C#.

using System;
using System.Windows.Forms;
using System.Net.Mail;

Now, within the Main() function, we will add our C# code.

Below C# code we used for this SSIS Script Task is there in the above screenshot.

using System;
using System.Windows.Forms;
using System.Net.Mail;

namespace ST_770e2ba10f4c4c3d8516ac9b5ad0f979
{
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
	[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
	public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
	{
		/// <summary>
        /// This method is called when this script task executes in the control flow.
        /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        /// To open Help, press F1.
        /// </summary>
		public void Main()
		{
            // TODO: Add your code here
            String mailFrom = Dts.Variables["Email_From"].Value.ToString();
            String mailFromPwd = Dts.Variables["Email_From_Pwd"].Value.ToString();
            String mailTo = Dts.Variables["Email_To"].Value.ToString();
            String mailSubject = Dts.Variables["Email_Subject"].Value.ToString();
            String mailBody = Dts.Variables["Email_Body"].Value.ToString();          
            
            try
            {
                MailMessage email = new MailMessage();
                email.From = new MailAddress(mailFrom);
                email.To.Add(mailTo);
                email.Subject = mailSubject;
                email.Body = mailBody;
                
                SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com");
                SmtpServer.Port = 587;
                SmtpServer.UseDefaultCredentials = false;
                SmtpServer.Credentials = new System.Net.NetworkCredential(mailFrom, mailFromPwd);
                SmtpServer.EnableSsl = true;

                SmtpServer.Send(email);
                MessageBox.Show("Email was Successfully Sent ");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

            Dts.TaskResult = (int)ScriptResults.Success;
		}

        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
	}
}

Now, we will close ScriptMain.cs file and script task editor once we finished editing.

Now, let’s run the package.

Now we can see on the above screenshot that the SSIS Script Task package executed successfully, and the message popped up as "Email was Successfully sent".

Now, let’s validate by opening the Gmail account.

In the above screenshot, we can see that we got the email with the same EmailSubject and EmailBody specified in the C# code.

Conclusion

In today’s article, we went through the Script task and learned how to send email using that. Script task is a very powerful tool and can be used to implement any such functionality that is not available already. It can also be used to extend any functionality that is available partially.

Hope you liked the article, please share your comments/ suggestions below.