How do I send the result set from a query as an attachment in an email using SSIS?

Probably, you have found an answer to your question by now. This answer is for others who might stumble upon this question. I don't think you can use object variable in expression. You need to loop through the query result object and format it to a string so that you can send the query output in an e-mail message. You can also export the data to a file and send the file as an attachment. That is another possible option. 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.

Step-by-step process:

1. Create a table named dbo.EmailData using the script provided under SQL Scripts section.

2. Screenshot #1 shows sample data that Execute SQL task will query and send it in an e-mail in this example.

3. On the SSIS package, create 5 variables as shown in screenshot #2.

4. 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.

5. Configure the Execute SQL task as shown in screenshots #3 and #4.

6. 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.

7. 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.

8. Configure the Send Email task as shown in screenshot #7. You need to configure it with valid email address in From and To fields.

9. After configuring the Control flow tasks, your package should look like as shown in screenshot #8.

10. Sample package execution is shown in screenshot #9.

11. 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.

Hope that helps.

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:

Screenshot #2:

Screenshot #3:

Screenshot #4:

Screenshot #5:

Screenshot #6:

Screenshot #7:

Screenshot #8:

Screenshot #9:

Screenshot #10:

After many attempts this method actually works. However, if you have Visual Studio 2010 or higher the C# script needs to be changed ever so slightly.

Replace:

[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]

with this:

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]

Still trying to sort out the spacing in the email I am getting but quite happy with the results.