ssis - Send all records from SQL Server table though send mail task in body -

i trying send table records in email body though send mail task

my flow:

1. i uses sql execute task fetch rows table , stored in object

2. uses each loop container , in use script task store rows in emailmessage body

3. i used send mail task send email

i getting last records of table in message body.

please guide me how send table data @ once in message body

actaul flow

error

i think take different approach , recurse recordset directly in script task looks work too. guess problem overwrite user::emailmessage @ every iteration. last few records looking @ code think 1 unless uncomment if (varcollection == string.empty) in case might more.

anyway, main offending problem

varcollection["user::emailmessage"].value = header; 

that resets emailmessage body header row time called.

edit: adding per comment reset message @ every new shipment number. add package variable prevshippingnum hold previous looped number test if same or has changed. make sure variable listed readwritevariable script task. modify script include this:

        dts.variabledispenser.getvariables(ref varcollection);          bool newmessage = (varcollection["user::prevshippingnum"].value != varcollection["user::shppingnum"].value) ? true : false;          if (string.isnullorwhitespace(varcollection["user::emailmessage"].value.tostring()) || newmessage)         {             varcollection["user::emailmessage"].value = string.format("{0}........");         }          varcollection["user::emailmessage"].value += string.format("{0}......"); 

the positive can use new variable constraint determine when send email task.

a different approach:

note pretty big edit add new sub take care of sending emails per shippingnum:

way proceed pass recordset variable using script task , let email message building. clear replace foreach loop! here code adapted 1 of solutions:

add reference system.data.datasetextensions

add following namespaces:

using system.data.oledb; using system.net.mail; using system.linq; using system.collections.generic;      private void main()     {         //using system.data.oledb;         oledbdataadapter oleadapter = new oledbdataadapter();         datatable dt = new datatable();         oleadapter.fill(dt, dts.variables["user::oledbrecordsetvar"].value);          //build header row         string headerrow = string.format("{0}........", "shippingnum ....");          //get distinct shippingnums         var shippingnums = (from datarow dr in dt.rows                             select (int)dr["shppingnum"]).distinct();          //now build differnt emails         foreach (var num in shippingnums)         {             string emailbody = headerrow;             list<datarow> emaillines = (from datarow dr in dt.rows                               (int)dr["shippingnum"] == num                               select dr).tolist<datarow>();              foreach (datarow line in emaillines)             {                 emailbody += string.format("{0}....", line["columnname1"].tostring(), line["columnname2"].tostring());             }              sendemail(emailbody);          }          dts.taskresult = (int)scriptresults.success;     }      private void sendemail(string messagebody)     {          //get smtp server address ssis connection manger         connectionmanager smtpconnectionmanager = dts.connections["name of smtp connection manager"];         //note trusted authentication if want use username , password have discovery         smtpclient emailclient = new smtpclient(smtpconnectionmanager.properties["smtpserver"].getvalue(smtpconnectionmanager).tostring());          mailmessage email = new mailmessage();         email.priority = mailpriority.normal;         email.isbodyhtml = false; //change true if send html                                   //can hard code addresses if desire use variables make more flexible         email.from = new mailaddress(dts.variables["user::fromaddress"].value.tostring());         email.to.add(dts.variables["user::toaddress"].value.tostring());         email.body = messagebody;         emailclient.send(email);     }