Send email to dynamic recipient SSIS Send Mail Task

Question

I have an SSIS package which is going to be deployed on test, staging, and finally production. It has a couple of "Send Mail Tasks",say 10.

As developer, I put my email address as the recipient of the email.

Currently, for the test person, I need to change all the "To"s in all the script task to e.g. "[email protected]". If following the paradigm of hard-coding the emails this way,I need to change the recipient email 30 times!!! (10 for each stage stated above)

Just wondering if there is any way to inject To field(recipient) dynamically. e.g. from a variable. like I have done for the "MessageSource"

Answer 1

You can set the ToLine of the Send Mail task to be the value of a variable, using an Expression from the Properties window of the task.

Answer 2

We use a SQL table containing a list of email recipients for various conditions (table columns of kemail, emailaddress, success, error) and set flags in the table to 0=no, 1=yes for that particular user to receive emails on particular conditions.

Then create a variable that contains a delimited list of your recipients then use an expression to set "ToLine" for the send mail task.

The only thing to watch here is that you don't end up with a no records returned from the SQL table. We always have our "support" email address always having all the bits set, to avoid this.

So the package wont need to be modified when a new user needs to receive email updates.