向动态收件人发送电子邮件 SSIS 发送邮件任务 [英] Send email to dynamic recipient SSIS Send Mail Task

问题描述

我有一个 SSIS 包，它将在测试、暂存和最终生产中部署.它有几个发送邮件任务"，比如 10 个.

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.

目前，对于测试人员，我需要将所有脚本任务中的所有收件人"更改为例如[e-mail]".如果按照这种方式对电子邮件进行硬编码，我需要更改收件人电子邮件 30 次！！！(上述每个阶段 10 个)

Currently, for the test person, I need to change all the "To"s in all the script task to e.g. "[e-mail]". 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)

只是想知道是否有任何方法可以动态注入 To field(recipient).例如从一个变量.就像我为MessageSource"所做的那样

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"

推荐答案

我们使用一个 SQL 表，其中包含各种条件下的电子邮件收件人列表(kemail、emailaddress、success、error 的表列)并在表中设置标志以0=否，1=是该特定用户在特定条件下接收电子邮件.

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.

然后创建一个包含分隔的收件人列表的变量，然后使用表达式为发送邮件任务设置ToLine".

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

这里唯一需要注意的是，您最终不会从 SQL 表返回无记录.我们总是让我们的支持"电子邮件地址始终设置所有位，以避免这种情况.

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.

这篇关于向动态收件人发送电子邮件 SSIS 发送邮件任务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！