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

Question

I am trying to send all the table records in email body though send mail task

My flow:

1. I uses SQL execute task to fetch the rows from table and stored in an object

2. Uses for each loop container and in that I use a script task to store the rows in an EmailMessage body

3. I used Send mail task to send the email

I am only getting last records of the table in the message body.

Please guide me how to send all the table data at once in a message body

Actaul flow

error

Answer 1

I think I would take a slightly different approach and recurse the recordset directly in the script task but this looks like it would work too. I would guess that your problem is that you overwrite User::EmailMessage at every iteration. You say you get last few records but looking at your code I would think you will get 1 unless you uncomment the IF (varcollection == string.empty) in which case you might get more.

Anyway, the main offending problem is

varCollection["User::EmailMessage"].Value = header;

That resets your EmailMessage body to the header row any time it is called.

Edit: Adding as per your comment to reset message at every new shipment number. Add another package variable PrevShippingNum which will hold the previous looped number to test if it is the same or has changed. Make sure that this variable is listed as ReadWriteVariable to the script task. then modify your script to include something like 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 about this is you can also use your new variable as a constraint to determine when to send email task.

A different Approach:

Note pretty big edit to add new sub to take care of sending emails per ShippingNum:

Way I would proceed pass the recordset variable you are using to a script task and let it do the email message building. Just to be clear this is to replace your foreach loop! here is some code adapted from one of my solutions:

Add Reference to 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 the Differnt Emails
        foreach (var num in shippingNums)
        {
            string emailBody = headerRow;
            List<DataRow> emailLines = (from DataRow dr in dt.Rows
                              where (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 the smtp server address from the SSIS connection manger
        ConnectionManager smtpConnectionManager = Dts.Connections["Name Of SMTP Connection Manager"];
        //note this is for trusted authentication if you want to use a username and password you will have to do some discovery
        SmtpClient emailClient = new SmtpClient(smtpConnectionManager.Properties["SmtpServer"].GetValue(smtpConnectionManager).ToString());

        MailMessage email = new MailMessage();
        email.Priority = MailPriority.Normal;
        email.IsBodyHtml = false; //change to true if you send html
                                  //can hard code addresses if you desire I use variables to make it 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);
    }