How to send email notification in SSIS package using Gmail SMTP Server

In many cases, email notification is mandatory for ETL development. However, most of the small business clients usually don’t have their own SMTP server, in this case you can use Gmail SMTP server. There are so many articles about this topic, but many of them having bugs in the code. Hope this would help to run bug free and easily understandable. Also this code works well in Azure virtual machines too.

Design SSIS job using with required parameters. In here, Email Body, Email Subject and Email To parameters/variables are used.

Create a Script task using below code, this code would help to send email with multiple recipients.

Actual C# Code:

public void Main()

{

SmtpClient smtp = new SmtpClient("smtp.gmail.com", 587);

smtp.EnableSsl = true;

smtp.UseDefaultCredentials = false;

smtp.Credentials = new NetworkCredential("uma@gmail.com", "*********");

MailMessage msg = new MailMessage();

msg.IsBodyHtml = true;

msg.From = new MailAddress("umashan@gmail.com");

string addresses = Dts.Variables["$Project::EmailTo"].Value.ToString();

foreach (var address in addresses.Split(new[] { ";" }, StringSplitOptions.RemoveEmptyEntries))

{

msg.To.Add(address);

}

msg.Subject = Dts.Variables["User::EmailSubject"].Value.ToString();

msg.Body = Dts.Variables["User::EmailBody"].Value.ToString();

smtp.Send(msg);

Dts.TaskResult = (int)ScriptResults.Success;

}

enum

ScriptResults

{ Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,

Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

};

Cheers!

Uma