SSIS script task - send different results to multiple recipients

Question

I am struggling to send a different body to multiple recepients.

Here's what I'm trying to do:

I have a SQL query that I am executing and getting the results in the "Execute SQL Task".

The result example:

The script looks like this:

public void Main()
    {
        // TODO: Add your code here
        Variables varCollection = null;
        Dts.VariableDispenser.LockForWrite("User::QueryResult");
        Dts.VariableDispenser.GetVariables(ref varCollection);
        string result = varCollection["User::QueryResult"].Value.ToString();
        var data = varCollection["User::QueryResult"].Value;
        

        OleDbDataAdapter da = new OleDbDataAdapter();
        DataTable dt = new DataTable();
        da.Fill(dt, varCollection["User::QueryResult"].Value);

        foreach (DataRow row in dt.Rows)
        {
            string body = string.Empty;
            string emailTo = string.Empty;
            string subject = string.Empty;
            try
            {
                List<string> final = new List<string>();
                foreach (DataRow row2 in dt.Rows)
                {
                    emailTo = row2["Email"].ToString();
                    subject = row2["SalesOrder"].ToString() + " products have been approved";
                    List<string> temp = new List<string>();
                    if (row2[2].ToString() == emailTo)
                    {
                        temp.Add(row2.ToString());
                        final.Add(string.Join("\t", temp));
                    }
                    
                    body = string.Join("\r\n", final);
                }
                SendMailMessage("[email protected]", emailTo, subject, body, true, credetnials);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

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

At the moment the result which I am getting is totally incorrect. With this script, all of the recepients will get whole DataTable.

I am trying to send the specific rows that contain the email "[email protected]" to this email.

So bascially two rows will be sent to "[email protected]" and only one to "[email protected]".

Please let me know if you have any questions. Looking forward to suggestions and answers! Thanks!

Answer 1

One option to consider, use the DataTable's Select method to find matching email records to process in your inner loop.

public void Main()
{
    // TODO: Add your code here
    Variables varCollection = null;
    Dts.VariableDispenser.LockForWrite("User::QueryResult");
    Dts.VariableDispenser.GetVariables(ref varCollection);
    string result = varCollection["User::QueryResult"].Value.ToString();
    var data = varCollection["User::QueryResult"].Value;

    OleDbDataAdapter da = new OleDbDataAdapter();
    DataTable dt = new DataTable();
    da.Fill(dt, varCollection["User::QueryResult"].Value);

    IList<string> workedEmails = new List<string>();
    foreach (DataRow row in dt.Rows)
    {
        string body = string.Empty;
        string emailTo = row[2].ToString().Trim();
        // already worked this email on a previous row; skip it this time
        if (workedEmails.Contains(emailTo)) continue;
        // filter rows for current email and use all matched rows to build message body
        string filter = $"Email = '{emailTo}'";
        DataRow[] matchingEmailRows = dt.Select(filter);
        foreach (DataRow matchedRow in matchingEmailRows)
        {
            string salesOrder = matchedRow[0].ToString();
            string subject = salesOrder + " products have been approved";
            string matchedEmail = matchedRow["Email"].ToString();
            if (emailTo == matchedEmail)
            {
                body += subject + Environment.NewLine;
            }
        }
    SendMailMessage("[email protected]", emailTo, subject, body, true, credetnials);
    workedEmails.Add(emailTo);
}