SSIS - Sending email different recipients different data

Question

I'm new in SSIS.

I have a table with 1500 rows and I need to send emails from that table but each recipients has 15 rows from the table.

So I need to send different data to different emails from the same query.

Can you guys could help me please?

Thanks in advance.

Leo

-------------------update------------------------

Guys I could create a SSIS package to send email to different recipients the problem is: sample: 2 different users is receiving emails for the number of rows that they have in database...that's terrible each customer has 15 lines it will be 15 emails can I send just one email for customer contains the whole data?

Thanks in advance...

Answer 1

I have a package that sole role is to send emails from my packages and record the results in to a table. I use this package over and over from any package that sends mail.

It is simply a script task, that takes parameters and does the work:

The script to process:

    public void Main()
    {
        //Read variables 
        #region ReadVariables
        string cstr = Dts.Variables["connString"].Value.ToString();
        //string sender = (string)Dts.Variables["User::Sender"].Value;
        string title = (string)Dts.Variables["$Package::Title"].Value;
        string priority = (string)Dts.Variables["$Package::Priority"].Value;
        string body = (string)Dts.Variables["$Package::Body"].Value;
        string source = Dts.Variables["$Package::Source"].Value.ToString();
        string directTo = Dts.Variables["$Package::DirectMail"].Value.ToString();
        string groups = Dts.Variables["$Package::MailGroups"].Value.ToString();



        #endregion


        //Send Email
        #region SendMail
        MailMessage mail = new MailMessage();
        //mail.From = new MailAddress(sender);
        mail.Subject = title;
        mail.Body = body;
        mail.IsBodyHtml = true;

        switch(priority.ToUpper())
        {
            case "HIGH":
                mail.Priority= MailPriority.High;
                priority = "High";
                break;
            default:
                mail.Priority=MailPriority.Normal;
                priority = "Normal";
                break;
        }

        DataTable dt = new DataTable(); //This is going to be a full distribution list

        //Fill table with group email
        if (groups.Split(',').Length > 0)
        {
            foreach (string group in groups.Split(','))
            {
                string strCmd = "mail.spGetEmailAddressesByGroup";
                using (OleDbConnection conn = new OleDbConnection(cstr))
                {
                    using (OleDbCommand cmd = new OleDbCommand(strCmd, conn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("A", group);
                        OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                        da.Fill(dt);
                    }
                }
            }
        }

        //add the directs to email
        if (directTo.Split(',').Length > 0)
        {
            foreach (string m in directTo.Split(','))
            {
                if (m != "")
                {
                    DataRow dr = dt.NewRow();
                    dr[0] = "TO";
                    dr[1] = m;
                    dt.Rows.Add(dr);
                }
            }
        }

        //Add from and reply to defaults
        DataRow dr2 = dt.NewRow();
        dr2[0] = "REPLYTO";
        dr2[1] = ""; //WHERE DO YOU WANT REPLIES
        dt.Rows.Add(dr2);
        DataRow dr3 = dt.NewRow();
        dr3[0] = "FROM";
        dr3[1] = ""; //ENTER WHO YOU WANT THE EMAIL TO COME FROM
        dt.Rows.Add(dr3);

        //Bind dt to mail
        foreach (DataRow dr in dt.Rows)
        {
            switch (dr[0].ToString().ToUpper())
            {
                case "TO":
                    mail.To.Add(new MailAddress(dr[1].ToString()));
                    dr[0] = "To";
                    break;
                case "CC":
                    mail.CC.Add(new MailAddress(dr[1].ToString()));
                    dr[0] = "Cc";
                    break;
                case "BCC":
                    mail.Bcc.Add(new MailAddress(dr[1].ToString()));
                    dr[0] = "Bcc";
                    break;
                case "REPLYTO":
                    mail.ReplyToList.Add(new MailAddress(dr[1].ToString()));
                    dr[0] = "ReplyTo";
                    break;
                case "FROM":
                    mail.From = new MailAddress(dr[1].ToString());
                    dr[0] = "From";
                    break;
                case "SENDER":
                    mail.Sender = new MailAddress(dr[1].ToString());
                    dr[0] = "Sender";
                    break;
                default:
                    dr[0] = "NotSent";
                    break;
            }
        }



        try
        {
            SmtpClient smtp = new SmtpClient();
            smtp.Port = 25;
            smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
            smtp.UseDefaultCredentials = false;
            smtp.Host = ""; //ENTER YOUR IP / SERVER 
            smtp.Send(mail);
        }
        catch (Exception e)
        {

        }
        #endregion

        //Record email as sent //I WILL NOT BE PROVIDING THIS PART
        //#region RecordEmailInDB

That's just to send mails, I have many packages that build the emails to send. Most are variables to parameters on the call. The most complicated is the building of the Email Body and this is where your specific question comes into play.

This is a sample control flow:

There's a data flow that queries the details that need to be sent and are recorded into an object. As well as a record counter.

Back to control flow. There is a precendence constraint set to rowcount >0.

The I have a script task to build the body basically. And I have a class that converts the ADO Object into an HTML table.

    public string BuildHTMLTablefromDataTable(DataTable t)
    {
        System.Text.StringBuilder sb = new System.Text.StringBuilder();
        sb.Append("<table border='1'><tr style='background-color: #1A5276; color:#FFFFFF;'>");

        foreach (DataColumn c in t.Columns)
        {
            sb.Append("<th align='left'>");
            sb.Append(c.ColumnName);
            sb.Append("</th>");
        }

        sb.Append("</tr>");

        int rc = 0;

        foreach (DataRow r in t.Rows)
        {
            rc++;
            //every other row switches from white to gray
            string OpeningTR = "<tr style='background-color: " + ((rc % 2 == 1) ? "#E5E7E9;'>" : "#FCF3CF;'>");
            sb.Append(OpeningTR);

            foreach (DataColumn c in t.Columns)
            {
                sb.Append("<td align='left'>");
                sb.Append(System.Web.HttpUtility.HtmlEncode(
                                                            r[c.ColumnName] == null ? String.Empty : r[c.ColumnName].ToString()
                                                            )); //This will handle any invalid characcters and convert null to empty string
                sb.Append("</td>");
            }

            sb.Append("</tr>");
        }

        sb.Append("</table>");

        return sb.ToString();
    }


    public string BuildBody(DataTable dt)
    {

        string body = "<P>The following are vouchers that are not in the voucher table but in the GL:</p>";

        DataView v = new DataView(dt);
        body += BuildHTMLTablefromDataTable(dt); //v.ToTable(true, "Name", "LastVisit", "DaysUntilTimeout", "ExpDate", "RoleName"));

        return body;
    }


    public void Main()
    {
        #region Read Variables
        System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter();
        DataTable dt = new DataTable();
        da.Fill(dt, Dts.Variables["User::Changes"].Value);
        #endregion

        string body = BuildBody(dt);

        Dts.Variables["User::Body"].Value = body;


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

Finally I will call the SendMail package and pass the parameters.

For your purpose you will need to have a foreach around this package and adjust your where clause for the person on each pass.

This is an example of an email sent (Body only):

Answer 2

This is going to vary somewhat based on the query and other specifications, but at a high level you're probably going to want to follow these steps for sending the emails using SSIS. This example assumes that the emails are stored in a column within this table. As others have pointed out, using sp_send_dbmail will likely be your best option.

• Create two string variables. One will hold the email addresses and the other will be for the SQL for sp_send_dbmail (more on this below). Create an additional variable of the object type that will hold the list of emails during execution.
• Modify the string variable that will hold the SQL for sp_send_dbmail to be an expression using the variable with the email names. Depending on the query, you may need to add additional variables for other parameters in this query. An example of this variable is at the end of this post.
• Have an initial Execute SQL Task that queries the table and retrieves the email addresses. Make sure to get all rows for each email. Set the ResultSet property to full and on the Result Set pane, add the object variable with 0 as the Result Name.
• Next add a Foreach Loop, use the Foreach ADO Enumerator type, and select the object variable from the last Execute SQL Task for the source variable. The Enumeration Mode can be left as the "Rows in the first table" option.

• On the Variable Mappings pane, add the string variable (for the email addresses) and set the index to 0. This will hold the email addresses for each execution of sp_send_dbmail.

• Within the Foreach Loop, add an Execute SQL Task. For this, you will need to set the SQLSourceType to variable and use a variable holding the SQL with sp_send_dbmail.

• Make sure that you have Database Mail properly configured for the account and profile used, including membership in the DatabaseMailUserRole role in msdb. You may also need to use the three-part name (database.schema.table) for your table.

Example SQL Variable Expression:

Note the double-quotes in the @query parameter around the email variable in addition to the quotes from concatenating the expression. You can either use two single quotes or precede a double-quote with a \ in the query to use a double-quote as part of the expression.

"DECLARE @Title varchar(100)

SET @Title = 'Email Title'

EXEC MSDB.DBO.SP_SEND_DBMAIL @profile_name = 'Your Profile',
 @recipients = 'YourEmail@test.org',  

@query = 'SELECT * FROM YourDatabase.YourSchema.YourTable WHERE EmailColumn = "" 
+ @[User::VariableWithEmailAddress]  + ""',
@query_result_no_padding = 1, @subject = @Title ; "