using SSIS script task to send email result

標籤：style   ar   io   color   os   sp   for   on   bs   

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

using SSIS script task to send email result