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

CodePudding user response：

Like below:

In SSIS Execute SQL Task

SQL Statement: EXEC dbo.usp_GenerateEmailBody ?

CodePudding user response：

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("[email protected]");
            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;