DEV Community

Venkatesh Jonnagadla
Venkatesh Jonnagadla

Posted on

SSIS - Send HTML Email from SSIS Job using DTSX package

#ssis #sqlserver #sqlserverintegrationservices

When you are working in automating the reports you often require sending the result sets as part of the email body. Unfortunately, there is no direct way in SSIS to do so. We need to use some trick to achieve this.
First take the result set into a package variable (ex-gStrResultSet). You must be using the execute sql task for this. The query in the execute sql task should return the XML form of the table. For which you need to use the below format for your query.

select
(select COL1 as td for XML path(''),type),
(select COL2 as td for XML path(''),type),
(select COL3 as td for XML path(''),type),
(select COL4 as td for XML path(''),type)
from
(SELECT COL1,COL2,COL3,COL4
FROM   dbo.Table
) tbl
FOR XML PATH('tr')
Enter fullscreen mode Exit fullscreen mode

Now you need to send the XML table as part of the email body along with the other text you need. As the email body is of HTML, you need to make changes in such a way that XML output fits into the HTML body of the email. The sample code(C# & HTML) goes like below-

public void Main()
                        {
            try
            {
                var emailBody = "<html><head></head><body>" +
                    "<p> Hi ,<br><br>Please find the table as HTML Report below.<br></p>" +                    
                    @"<table border=""1"" cellpadding=""2"" style=""border-collapse: collapse;"">" +
                    @"<tr style=""color:black;background-color:skyblue;"">" +
                    "<th> COL1 </th>" +
                    "<th> COL2 </th>" +                    
                    "<th> COL3 </th>" +
                    "<th> COL4 </th>" +                   
                    "</tr>";

                // Ensure variable exists and is not null
                var htmlTable = Dts.Variables["User::gStrResultSet"].Value?.ToString() ?? "";
                emailBody += htmlTable.Replace("<ROOT>", "").Replace("</ROOT>", "");
                emailBody += "</table><br><br><p>Regards,<br>SSIS Job</p></body></html>";

                string smtpServer = "Test.orgnaization.COM"; // get it from your organization
                int smtpPort = 25;


                string reportTitle = "HTML Report";
                string todayDate = DateTime.Now.ToString("MM/dd/yyyy");
                string subject = $"{reportTitle} - {todayDate}";


                string senderEmail = notification@organization.com;
                string recipientEmail = recipient@organization.com;


                MailMessage mail = new MailMessage(senderEmail, recipientEmail, subject, emailBody)
                {
                    IsBodyHtml = true
                };

                SmtpClient smtpClient = new SmtpClient(smtpServer, smtpPort)
                {
                    DeliveryMethod = SmtpDeliveryMethod.Network,
                    UseDefaultCredentials = true
                };
                mail.To.Add(toaddress1@organization.com);
                mail.To.Add(toaddress2@organization.com);                

                mail.CC.Add(ccAddress1@organization.com);
                mail.CC.Add(ccAddress2@organization.com);

                smtpClient.Send(mail);
                Dts.TaskResult = (int)ScriptResults.Success;

            }
            catch (Exception ex)
            {
                Dts.TaskResult = (int)ScriptResults.Failure;
            }            
                        }
Enter fullscreen mode Exit fullscreen mode

Hope this helps!!!! Thank you!!!!!!!!!

Top comments (0)