How to send the records from a table in an e-mail body using SSIS package?

Step-1 : Create a Object data type variable which will hold the result set of a given table and a string type variable to hold the recipient email IDs.

Step-2 : Use a Execute SQL Task Editor and choose the Result set option as Full result set and a query statement to fetch the data from a given table. Use the object variable to hold the result set of your table as shown in screen shots below:

Step-3 :Take a Script task Editor and use Recepient_email_id variable as read only variable and Use the below C# script to send an email.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Linq;
using System.Collections.Generic;
using System.Collections;
using System.Data.OleDb;
using System.Net.Mail;
using System.Net;
#endregion

namespace ST_a5f34f5fc36645b6bd90a5b8887ac589
{
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
    [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()
        {
            Variables varCollection = null;

            string User_Recepient_Email_ID = Dts.Variables["User::Recepient_Email_ID"].Value.ToString();

            Dts.VariableDispenser.LockForWrite("User::Test_Table_Result");
            Dts.VariableDispenser.GetVariables(ref varCollection);
            var data = varCollection["User::Test_Table_Result"].Value;

            OleDbDataAdapter da = new OleDbDataAdapter();
            DataTable dt = new DataTable();
            da.Fill(dt, varCollection["User::Test_Table_Result"].Value);

            SendMailMessage("loadJob@xyz.com", User_Recepient_Email_ID, "ETL Load Status Report",ConvertDataTableToHTML(dt),true,"174.18.10.122");

            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;
        }
        private void SendMailMessage(string From, string SendTo,string Subject, string Body,bool IsBodyHtml,string Server)
        {
            MailMessage htmlMessage;
            SmtpClient mySmtpClient;

            htmlMessage = new MailMessage(From, SendTo, Subject, Body);
            htmlMessage.IsBodyHtml = IsBodyHtml;

            mySmtpClient = new SmtpClient(Server);
            mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials;
            mySmtpClient.Send(htmlMessage);
        }   


    }
}

Please note : You need to change the sender email id, email subject and server IP address for the below statement in above script.

SendMailMessage("loadJob@xyz.com", User_Recepient_Email_ID, "ETL Load Status Report",ConvertDataTableToHTML(dt),true,"174.18.10.122");

Below is the format of email which you receive.