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

i have result set execute sql task query saved on system.object variable , send results using send mail task using following expression on messagesource.

"please find attached data summary\n\n" + substring( @[user::myvariable] ,1,3990)
+ "\n\n"

probably, have found answer question now. answer others might stumble upon question. don't think can use object variable in expression. need loop through query result object , format string can send query output in e-mail message. can export data file , send file attachment. possible option. example shows how loop through query result set form message body emailed using send email task.

step-by-step process:

create table named dbo.emaildata using script provided under sql scripts section.
screenshot #1 shows sample data execute sql task query , send in e-mail in example.
on ssis package, create 5 variables shown in screenshot #2.
on ssis package, place following tasks: execute sql task, foreach loop container, script task within foreach loop container , send email task.
configure execute sql task shown in screenshots #3 , #4.
configure foreach loop container shown in screenshots #5 , #6. variable mappings section shows order in query result columns appear , how assigned ssis variables. these variables used form email message inside script task.
in script task, replace code 1 shown under script task code section. script task has simple plain text email message formatting.
configure send email task shown in screenshot #7. need configure valid email address in from , to fields.
after configuring control flow tasks, package should shown in screenshot #8.
sample package execution shown in screenshot #9.
e-mail sent package shown in screenshot #10. information have been removed screenshot. can compare table data shown in screenshot #1 email output , should same.

hope 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 can used in ssis 2008 , above. .

/*microsoft sql server integration services script task    write scripts using microsoft visual c# 2008.    scriptmain entry point class of 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 header message 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 query result 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: