Send the n Different type of mail based on the product category name to respective product sellers

We have a requirement that we need to send the sales summary of the product on the respective sellers.

For the demo we are using the AdventureWorksDW Database.

We have product sales information.

SELECT pc.productcategorykey,        pc.englishproductcategoryname AS Productcategoryname,        Sum(f.unitprice)              AS TotalUnitPrice,        Sum(f.extendedamount)         AS TotalExtendedAmount,        Sum(f.unitpricediscountpct)   AS TotalUnitPriceDiscountPct,        Sum(f.discountamount)         AS TotalDiscountAmount,        Sum(f.productstandardcost)    AS TotalProductStandardCost,        Sum(f.totalproductcost)       AS TotalProductCost,        Sum(f.taxamt)                 AS TotalTaxAmt,        Sum(f.freight)                AS TotalFreight,        Sum(f.salesamount)            AS Totalsales FROM   dimproductcategory pc        INNER JOIN dimproductsubcategory Psc                ON PC.productcategorykey = psc.productcategorykey        INNER JOIN dimproduct p                ON p.productsubcategorykey = pc.productcategorykey        INNER JOIN factinternetsales f                ON f.productkey = p.productkey GROUP  BY pc.productcategorykey,           pc.englishproductcategoryname

   

We need to send the mail to respective user.   

We have user table.  

We are mapping the product category key with user id and sending that sales summary.

Mail Subject is :  Product name sales Report .

In mail greetings

·         If there is one email id then

o   Hello user name

·         If there are two email id then

o   Hello User name1 & User name2

·         If more than 2 then

o   Hello Team

We are writing the query to get the email id with the product sales summery details.

SELECT Product_details.productcategorykey,        Product_details.productcategoryname,        Product_details.totalunitprice,        Product_details.totalextendedamount,        Product_details.totalunitpricediscountpct,        Product_details.totaldiscountamount,        Product_details.totalproductstandardcost,        Product_details.totalproductcost,        Product_details.totaltaxamt,        Product_details.totalfreight,        Product_details.totalsales,        email.username,        email.useremailid FROM   (SELECT pc.productcategorykey,                pc.englishproductcategoryname AS Productcategoryname,                Sum(f.unitprice)              AS TotalUnitPrice,                Sum(f.extendedamount)         AS TotalExtendedAmount,                Sum(f.unitpricediscountpct)   AS TotalUnitPriceDiscountPct,                Sum(f.discountamount)         AS TotalDiscountAmount,                Sum(f.productstandardcost)    AS TotalProductStandardCost,                Sum(f.totalproductcost)       AS TotalProductCost,                Sum(f.taxamt)                 AS TotalTaxAmt,                Sum(f.freight)                AS TotalFreight,                Sum(f.salesamount)            AS Totalsales         FROM   dimproductcategory pc                INNER JOIN dimproductsubcategory Psc                        ON PC.productcategorykey = psc.productcategorykey                INNER JOIN dimproduct p                        ON p.productsubcategorykey = pc.productcategorykey                INNER JOIN factinternetsales f                        ON f.productkey = p.productkey         GROUP  BY pc.productcategorykey,                   pc.englishproductcategoryname) Product_details        LEFT JOIN (SELECT t1.productcategorykey               AS                          ProductCategoryKey,                          Stuff((SELECT ',' + t.username                                 FROM   usertbl AS t                                 WHERE  t.productcategorykey                                        = t1.productcategorykey                                 ORDER  BY t.productcategorykey                                 FOR xml path('')), 1, 1, '') AS Username,                          Stuff((SELECT ',' + t.useremailid                                 FROM   usertbl AS t                                 WHERE  t.productcategorykey                                        = t1.productcategorykey                                 ORDER  BY t.productcategorykey                                 FOR xml path('')), 1, 1, '') AS Useremailid                   FROM   usertbl t1                   GROUP  BY t1.productcategorykey) email               ON email.productcategorykey = Product_details.productcategorykey

  

Now we are designing the package.

Taking Execute SQL Task and running the above script and storing this result in the object variable.

  

Storing this result in the object variable.

   

Now taking the Script task to send the mail.

  

We are wring the below C# script to send the mail.

public void Main()                                 {                        DataTable dt = new DataTable();             var oleDa = new OleDbDataAdapter();             oleDa.Fill(dt, Dts.Variables["User::ProductDeatils"].Value);             string smtpAddress = "smtp.gmail.com";             int portNumber = 587;             bool enableSSL = true;             string emailFromAddress = "***********@gmail.com"; //Sender Email Address             string password = "*********"; //Sender Password                      foreach (DataRow row in dt.Rows)             {                 try                 {                                        string emailToAddress = row.ItemArray[12].ToString(); //Receiver Email Address                     string subject = row.ItemArray[1].ToString()+ " Sales Report";                     string body = " ";                     body = "Hello ";                     string[] words = row.ItemArray[11].ToString().Split(',');                     int i = words.Length;                     if (i == 1)                     {                         body = body + row.ItemArray[11].ToString()+",";                     }                     else if (i == 2)                     {                           body = body + words[0].ToString() + " and " + words[1].ToString() + ",";                     }                     else                     {                         body = body + " Team,";                     }                       body = body + Environment.NewLine +Environment.NewLine+ row.ItemArray[1].ToString() + " Report deatil as below.";                                                    using (MailMessage mail = new MailMessage())                     {                         mail.From = new MailAddress(emailFromAddress);                         mail.To.Add(emailToAddress);                         mail.Subject = subject;                         mail.Body = body;                                                                      mail.IsBodyHtml = true;                         mail.Body += " <html>";                         mail.Body += "<body>";                         mail.Body += "<table border=2>";                         mail.Body += "<tr>";                         mail.Body += "<td bgcolor=#00FFFF>Product category name</td><td bgcolor=#00FFFF>Total Unit Price</td>"+                             "<td bgcolor =#00FFFF>Total Extended Amount</td><td bgcolor=#00FFFF>Total Unit Price Discount Pct</td>" +                             "<td bgcolor =#00FFFF>Total Discount Amount</td><td bgcolor=#00FFFF>Total Product Standard Cost</td>" +                             "<td bgcolor =#00FFFF>Total Product Cost</td><td bgcolor=#00FFFF>Total TaxAmt</td>"+                             "<td bgcolor =#00FFFF>Total Freight</td><td bgcolor=#00FFFF>Tota lsales</td>";                             mail.Body += "</tr>";                                                mail.Body += "<tr>";                         mail.Body += "<td>" + row.ItemArray[1].ToString() + "</td>";                         mail.Body += "<td>" + row.ItemArray[2].ToString() + "</td>";                         mail.Body += "<td>" + row.ItemArray[3].ToString() + "</td>";                         mail.Body += "<td>" + row.ItemArray[4].ToString() + "</td>";                         mail.Body += "<td>" + row.ItemArray[5].ToString() + "</td>";                         mail.Body += "<td>" + row.ItemArray[6].ToString() + "</td>";                         mail.Body += "<td>" + row.ItemArray[7].ToString() + "</td>";                         mail.Body += "<td>" + row.ItemArray[8].ToString() + "</td>";                         mail.Body += "<td>" + row.ItemArray[9].ToString() + "</td>";                         mail.Body += "<td>" + row.ItemArray[10].ToString() + "</td>";                           mail.Body += "</tr>";                                               mail.Body += "</table>";                         mail.Body += "<br/><br/>";                         mail.Body += "Thank and Regards,<br/>";                         mail.Body += "DWH Load Team <br/>";                         mail.Body += "XYZ</body>";                         mail.Body += "</html>";                           using (SmtpClient smtp = new SmtpClient(smtpAddress, portNumber))                         {                             smtp.Credentials = new NetworkCredential(emailFromAddress, password);                             smtp.EnableSsl = enableSSL;                             smtp.Send(mail);                         }                     }                 }                   catch (Exception ex)                 {              Dts.Events.FireError(0, "Fire Error", "An error occurred: " + ex.Message.ToString(), "", 0);                     Dts.TaskResult = (int)ScriptResults.Failure;                 }               }             Dts.TaskResult = (int)ScriptResults.Success; }

 Now Package is ready to run.   

Running the package now.

Clothing Sales Report. 

Other user mail box   

Other user mail 

Other user 

Get the expected result.