Email with HTML content not showing up properly

Question

I am trying to query the database and send the content in the body of the email. The content is Order and the details for each users. I am trying to call a script task to compose the body and I am using the content in the Send Email Task. The script is like below

namespace ST_c2e68b3edd6842c4a6554376987c97c1
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        }

        public void Main()
        {
            var data = Dts.Variables["User::EmailData"].Value;

            OleDbDataAdapter da = new OleDbDataAdapter();
            DataTable dt = new DataTable();
            da.Fill(dt, data);

            Dts.Variables["User::EmailMessage"].Value = ConvertDataTableToHTML(dt);
            Dts.TaskResult = (int)ScriptResults.Success;
        }


        public static string ConvertDataTableToHTML(DataTable dt)
        {
            string html = "<table border ='1'>";
            //add header row
            html += "<tr>";
            for (int i = 0; i < dt.Columns.Count; i++)
                html += "<th>" + dt.Columns[i].ColumnName + "</th>";
            html += "</tr>";
            //add rows
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                html += "<tr style='color:blue;'>";
                for (int j = 0; j < dt.Columns.Count; j++)
                    html += "<td>" + dt.Rows[i][j].ToString() + "</td>";
                html += "</tr>";
            }
            html += "</table>";
            return html;
        }
    }
}

Where the User::EmailMessage is a string which I am using the Email body like

But in my email the body looks like

How can I show the HTML in the email properly. Any help is greatly appreciated

I already showed you how to compose an email correctly: https://stackoverflow.com/questions/70187327/ssis-script-task-is-throwing-deadlock-error/70188530#70188530 You just picked a different solution as an Answer. Just save the `@xhtmlBody` as a *.html file on the file system, and open it in a browser to test. — Yitzhak Khabinsky, Dec 02 '21 at 02:40
@YitzhakKhabinsky Yes I couldnt do the Stored Procedure thing successfully working :-( As I never done the SP before I am lil scared to go with that approach, thought I could get this one working.. The only issue I am seeing is the HTML tags.. Yes I tried saving the Body as .html file and it show up as table.. Not sure why in email it shows this way — user4912134, Dec 02 '21 at 02:49
@YitzhakKhabinsky Thank you I will connect with you.. But there is no way this solution will show the content properly in the email? — user4912134, Dec 02 '21 at 02:53
I am going to try to SP but I am not sure how to dynamically pass the value in where clause when I try `mo.SAMAccountEmail = ?` it throws error — user4912134, Dec 02 '21 at 03:10

score 0 · Answer 1 · answered Dec 02 '21 at 03:19

0

Like below:

In SSIS Execute SQL Task

SQL Statement: EXEC dbo.usp_GenerateEmailBody ?

answered Dec 02 '21 at 03:19

Yitzhak Khabinsky

18,471
2
15
21

score 0 · Accepted Answer · answered Dec 02 '21 at 19:05

I was able to get the solution working instead of the calling the Send email Task, within the Script Task I am sending the email like below

private void SendEmail(string messageBody, string userEmailId)
        {
            ConnectionManager smtpConnectionManager = Dts.Connections["SMTP Connection Manager"];
            SmtpClient emailClient = new SmtpClient(smtpConnectionManager.Properties["SmtpServer"].GetValue(smtpConnectionManager).ToString());

            MailMessage email = new MailMessage();
            email.Priority = MailPriority.Normal;
            email.IsBodyHtml = true;
            email.From = new MailAddress("d@abc.org");
            email.To.Add(userEmailId);
            email.Subject = "Order Details - " + DateTime.Now.ToString("dd-MM-yyyy");
            email.Body = messageBody;
            emailClient.Send(email);
        }
    }

This setting helps the email show the html properly in the email email.IsBodyHtml = true;

Email with HTML content not showing up properly

2 Answers2