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

Here is one possible option. Following example might give you an idea of how you can achieve emailing a result set using Send Email task. This example shows how to loop through the query result set to form the message body that will then be emailed using Send Email task.

If you don’t want to send e-mail when the result set is blank, you can add an Expression to the precedence constraint between Loop resultset and Send email tasks.

The example uses SSIS 2008 R2 and SQL Server 2008 R2 database.

Step-by-step process:

Create a table named dbo.EmailData using the script provided under SQL Scripts section.
Screenshot #1 shows sample data that Execute SQL task will query and send it in an e-mail in this example.
On the SSIS package, create 5 variables as shown in screenshot #2.
On the SSIS package, place the following tasks: Execute SQL task, Foreach loop container, Script task within the Foreach loop container and Send Email task.
Configure the Execute SQL task as shown in screenshots #3 and #4.
Configure the Foreach loop container as shown in screenshots #5 and #6. Variable mappings section shows the order in which the query result columns appear and how they are assigned to SSIS variables. These variables will be used to form the email message inside the Script task.
In the Script task, replace the code with the one shown under the Script task code section. The script task has very simple plain text email message formatting.
Configure the Send Email task as shown in screenshot #7. You need to configure it with valid email address in From and To fields.
After configuring the Control flow tasks, your package should look like as shown in screenshot #8.
Sample package execution is shown in screenshot #9.
E-mail sent by the package is shown in screenshot #10. Some information have been removed from the screenshot. You can compare the table data shown in screenshot #1 with this email output and they should same.

SQL Scripts:
.

CREATE TABLE [dbo].[EmailData](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemId] [varchar](255) NOT NULL,
    [ItemName] [varchar](255) NOT NULL,
    [ItemType] [varchar](255) NOT NULL,
    [IsProcessed] [bit] NULL,
 CONSTRAINT [PK_EmailData] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

Script task code:

C# code that can be used only in SSIS 2008 and above.
.

/*Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_7f59d09774914001b60a99a90809d5c5.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            Variables varCollection = null;
            string header = string.Empty;
            string message = string.Empty;

            Dts.VariableDispenser.LockForWrite("User::EmailMessage");
            Dts.VariableDispenser.LockForWrite("User::ItemId");
            Dts.VariableDispenser.LockForWrite("User::ItemName");
            Dts.VariableDispenser.LockForWrite("User::ItemType");
            Dts.VariableDispenser.GetVariables(ref varCollection);

            //Set the header message for the query result
            if (varCollection["User::EmailMessage"].Value == string.Empty)
            {
                header = "Execute SQL task output sent using Send Email Task in SSIS:\n\n";
                header += string.Format("{0}\t{1}\t\t\t{2}\n", "Item number", "Item name", "Item type");
                varCollection["User::EmailMessage"].Value = header;
            }

            //Format the query result with tab delimiters
            message = string.Format("{0}\t{1}\t{2}",
                                        varCollection["User::ItemId"].Value,
                                        varCollection["User::ItemName"].Value,
                                        varCollection["User::ItemType"].Value);

            varCollection["User::EmailMessage"].Value = varCollection["User::EmailMessage"].Value + message;

            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

Screenshot #1: