sometimes notification email is required to be sent so that receivers can know about the data load status. Following C# code in SSIS script task is 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::Environment

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

        }

    }