Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
548 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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. |
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?
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.
The first thing to do is to create two variables:
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 |
The next step is to read the rows into your ViewRows variable. First add an Execute SQL task:
Add a task to read in the rows.
Now edit this task to store the results:
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:
You'll need first to click on the Add button to add a results set into the grid.
You now need to add a FOREACH loop to loop over each row:
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:
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 SQL statement returned only one column - we capture the value of this in the string variable each time round the loop.
Having done the hard part, we can now send each email. First add a Send Mail task within the loop:
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 property can't take an expression.
What you can do though is to add an expression:
Use this tab to add an expression.
Choose to add 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:
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 ... !
Some other pages relevant to the above blog include:
From: | shadowolf654 |
When: | 13 Oct 16 at 12:08 |
My send mail task keeps telling me "No recipient is specified". Any advice please?
From: | Andy B |
When: | 13 Oct 16 at 20:55 |
No idea, I'm afraid. What I'd probably do is to right-click on the Send Email task and choose to edit its breakpoints. Put ones on for pre- and post-execute, and then use the Debug menu to display the Locals window. You can then check that the value of your variables are being correctly set.
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.