How to send an email to every person in a SQL Server table
This blog explains how to send an email to every person returned from a SQL query.

Posted by Andy Brown on 30 January 2015

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Using SSIS to send emails to a table of people

Another blog arising from a question from a recent course (it's how I learn): how do you send emails to people whose addresses are held in SQL Server?

View to show email addresses

Suppose that you have created a table or view to hold a number of email addresses, and you want to send the same email to each.

The rest of this blog assumes that you have configured SSIS to allow outgoing emails.

Step 1 - Creating the variables needed

The first thing to do is to create two variables:

Variables needed

The two variables we'll need.

Here's what these will be used for:

Variable Use
ViewRows This will hold the rows from the table or view
EmailAddress This will hold each individual email address

Step 2 - read the rows into the object variable

The next step is to read the rows into your ViewRows variable.  First add an Execute SQL task:

Execute SQL task

Add a task to read in the rows.

Now edit this task to store the results:

Read results into variable

The task selects one column from our view, and puts the data returned into a results set.

Now use the Result Set tab to specify that you'll store the first (and only) results set in our object variable:

Result set tab

You'll need first to click on the Add button to add a results set into the grid.

Step 3 - looping over each row

You now need to add a FOREACH loop to loop over each row:

Looping over rows

This task will run once for each row in the results set.

 

Edit this task to loop over the ADO rows stored in the ViewRows variable:

The Collection tab

These settings are in the Collection tab for the FOREACH loop.

On the Variable Mappings tab, choose to store the first (and as it happens, only) bit of information for each row in the EmailAddress variable:

The Variable Mappings tab

The SQL statement returned only one column - we capture the value of this in the string variable each time round the loop.

Step 4 - sending the email

Having done the hard part, we can now send each email.  First add a Send Mail task within the loop:

Send mail task

This task will send an email for each pass through the loop.

 

What we'd like to do now is put the variable name in as the To field for the email, but SSIS won't let us do this:

The To field

The To property can't take an expression.

What you can do though is to add an expression:

Go to the expressions tab

Use this tab to add an expression.

Choose to add an expression:

Adding an expression

Click on this button to the right of the dialog box to create a new expression.

 

You can now assign the variable to the To field:

The final expression

Success! We've assigned each email address to the To line of each email.

 

Run your package - but note that Wise Owl do not condone using this knowledge to send out spam ... !

This blog has 0 threads Add post