Monday, February 13, 2012

SSIS send e-mail from a SQL table

Hi, This is simple but people ask about it all the time...instead of keep writing the steps, I finally decided to blog about it.

you have a table that holds some names along with their e-mail addresses.

You'll need 2 variables, one of type OBJECT, one of type STRING

1-Add a "Execute SQL Task"
  • Add the connection
  • Add your query that brings the e-mail addresses
  • Get back the FULL RESULT SET




  •  In the result set, use your OBJECT type variable, to hold the result set



2- Add Foreach Loop Container
  • Use Foreach ADO
  • Select your OBJECT variable
  • Rows in the first table


  • In the variables mapping, map your STRING variable.

Now the container will iterate over the OBJECT variable and shred the contents into the STRING variable.


Now add your SEND MAIL TASK, configure it, then on the expressions, select To LINE and click on the ellipsis, add your STRING variable...


Now it'll iterate over the rows and send a mail one by one to the TO LINE from the string variable.

Hope that helps someone..

3 comments:

  1. Hi i am getting an error which states that no recipient is specified..please help i have used the exact same steps...

    ReplyDelete
    Replies
    1. Try setting the DELAY VALIDATION to TRUE, for the SEND MAIL TASK.

      Delete
  2. Very helpful, thank you!

    I have a question: how can I send one email with a bunch of addresses separated by semicolon rather than sending them one by one??

    ReplyDelete