Using SSIS Script Task to send email result

Sometimes notification email is required to being sent so, receivers can know about the data load status. Following C # code in SSIS Script task was composed to meet the requirement.

1. Drag a SQL task to get data result and assigned full set to an object variable (e.g.ocompletefilelist)

2. Drag a Script task to compose HTML message body and send email

Using System;
Using System.Data;
Using Microsoft.SqlServer.Dts.Runtime;
Using System.Windows.Forms;
Using System.Data.OleDb;
Using System.Net.Mail;

public void Main ()
{
//Todo:add your code here

//variable List
//user::ccseller,user::semailfrom,user::nfilecount,user::ocompletefilelist,user::semailtolist,user::enviro Nment
String vseller = dts.variables["Ccseller"]. Value.tostring ();
String vemailfrom = dts.variables["Semailfrom"]. Value.tostring ();
String vemailto = dts.variables["Semailtolist"]. Value.tostring ();
Object vfilelist = dts.variables["Ocompletefilelist"]. Value;
int vfilecount = (int) dts.variables["Nfilecount"]. Value;
String venv = dts.variables["Environment"]. Value.tostring ();

//setup SMTP Connection
SmtpClient vsmtp = new SmtpClient ();
String vsmtpcm = dts.connections["SMTP Connection Manager"]. Connectionstring.tostring ();
Vsmtp.host = (Vsmtpcm.split (';') [0]). Split (' = ') [1];
Vsmtp.usedefaultcredentials = true;
MailMessage vmsg = new MailMessage ();
//send from
Vmsg. from = new MailAddress (vemailfrom);
//send to
Array vtolist = Vemailto.split (';');
foreach (string s in vtolist)
{
Vmsg. To.add (new MailAddress (s));
}
//message subject and message body
Vmsg. Isbodyhtml = true;
OleDbDataAdapter Oleda = new OleDbDataAdapter ();
DataTable dt = new DataTable ();
string rc = "", Msgxml = "", HD = "";
string newline = Environment.NewLine;
String blank4 = "    ", Blank1 = " ";

if (Vfilecount = = 0)
{
Msgxml = "No files were loaded";
Vmsg. Subject =venv + ":" + Vseller + "-No files were loaded.";
}
Else
{
//read SQL Result
Oleda.fill (DT, vfilelist);
//compose table Header
foreach (DataColumn col in dt. Columns)
{
HD = HD + "<th style= ' border:1px solid black ' >" + Col. ColumnName + "</th>";
}
HD = "<tr style= ' background-color: #4F81BD; Color:white ' >" + HD + "</tr>" + newline;
//compose Table Content
foreach (DataRow row in dt. Rows)
{
rc = "";
foreach (DataColumn col in dt. Columns)
{
if (Col. Ordinal! = dt. COLUMNS.COUNT-1)
{
rc = rc + "&LT;TD style= ' border:1px solid ' >" + Blank1 + row[col. Ordinal]. ToString () + Blank4 + "</td>";
}
Else
{
if (Row[col. Ordinal]. ToString (). ToUpper () = = "SUCCESS")
{
rc = rc + "<td style= ' border:1px solid;background-color:green ' >" + Blank1 + row[col. Ordinal]. ToString () + Blank4 + "</td>";
}
Else
{
rc = rc + "<td style= ' border:1px solid;background-color:red ' >" + Blank1 + row[col. Ordinal]. ToString () + Blank4 + "</td>";
}
}
}
Msgxml = Msgxml + "<tr>" + rc + "</tr>" + newline;
}
//compose Final XML
Msgxml = "<table cellspacing=50 style= ' border:1px solid;border-collapse:collapse ' >" + newline + HD + msgxml + "</ Table> ";
Vmsg. Subject = venv + ":" + Vseller + "-Detail loaded Files list";
}
Vmsg. Body = Msgxml;
//send Email
Vsmtp.send (VMSG);

Dts.taskresult = (int) scriptresults.success;
}
}

Using SSIS Script Task to send email result