Send Mail in SSIS for each record

[VSLA]

I would like to accomplish the following:

1. Send Mail to an email address and individual's name retrieved from a list
retrieved by a select statement.
2. Customize the text in the email to incorporate the individual's name
(e.g. Robert or Sarah).
3. Add an variable attachment file (filename and path) based upon the list
retrieved by the select statement in item 1.
4. Write a line to a text file or database for each record processed and the
date and time it was processed.

I presume this should be done in a for each loop. Any similar eexamples out
there?

[Todd C]

This can all be done inside SSIS and with the stock tasks. Though you may
need to do a little customizing with a Script Task.

Set up your package with the following:
An Execute SQL Task that queries your database to retrieve Email TO,
Name(s), File path and string, etc. Set the Result Set property to "Full
Result Set" and on the Result Set page, map the results a variable of type
Object. This will load your query results into a table variable that SSIS can
use.

Next is a For Each loop. On the Collection page, set the Enumerator property
to "Foreach ADO Enumerator", the mode to "Rows in the first table", and
select the ADO object from the list. Use your Object variable from above. On
the Variable Mappings page, map every column to a new variable using a zero
index (if you have 5 columns, then name them 0 through 4).

Inside the loop, have your Send Mail Task. Click on the Expressions page and
build expressions for Email To, Message Source, and what ever else you need
(like File attachement, etc).
Next inside the loop would be an Execute SQL task with a statement of
"INSERT INTO TrackingTable . . .VALUES (?, ?...)" and map the parameters to
your varialbes.

Hints: Use Breakpoints to pause execution at various points and examine the
contents of your variables to make sure they are changing on every iteration
of the loop.

HTH
--
Todd C
MCTS SQL Server 2005

Post by VSLA
1. Send Mail to an email address and individual's name retrieved from a list
retrieved by a select statement.
2. Customize the text in the email to incorporate the individual's name
(e.g. Robert or Sarah).
3. Add an variable attachment file (filename and path) based upon the list
retrieved by the select statement in item 1.
4. Write a line to a text file or database for each record processed and the
date and time it was processed.
I presume this should be done in a for each loop. Any similar eexamples out
there?

[VSLA]

Thank you, Todd.

Post by Todd C
This can all be done inside SSIS and with the stock tasks. Though you may
need to do a little customizing with a Script Task.
An Execute SQL Task that queries your database to retrieve Email TO,
Name(s), File path and string, etc. Set the Result Set property to "Full
Result Set" and on the Result Set page, map the results a variable of type
Object. This will load your query results into a table variable that SSIS can
use.
Next is a For Each loop. On the Collection page, set the Enumerator property
to "Foreach ADO Enumerator", the mode to "Rows in the first table", and
select the ADO object from the list. Use your Object variable from above. On
the Variable Mappings page, map every column to a new variable using a zero
index (if you have 5 columns, then name them 0 through 4).
Inside the loop, have your Send Mail Task. Click on the Expressions page and
build expressions for Email To, Message Source, and what ever else you need
(like File attachement, etc).
Next inside the loop would be an Execute SQL task with a statement of
"INSERT INTO TrackingTable . . .VALUES (?, ?...)" and map the parameters to
your varialbes.
Hints: Use Breakpoints to pause execution at various points and examine the
contents of your variables to make sure they are changing on every iteration
of the loop.
HTH
--
Todd C
MCTS SQL Server 2005

Post by VSLA
1. Send Mail to an email address and individual's name retrieved from a list
retrieved by a select statement.
2. Customize the text in the email to incorporate the individual's name
(e.g. Robert or Sarah).
3. Add an variable attachment file (filename and path) based upon the list
retrieved by the select statement in item 1.
4. Write a line to a text file or database for each record processed and the
date and time it was processed.
I presume this should be done in a for each loop. Any similar eexamples out
there?