如何使用 SSIS 包从电子邮件正文中的表格发送记录?

本文介绍了如何使用 SSIS 包从电子邮件正文中的表格发送记录?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一张名为 Product 的表.

I have one table called Product.

产品表有更多记录，有时表没有记录.

The product table have more records, Some time the table not have record.

所以我想检查产品表，

if it's have the records i send all table information as mail.

if it's not have record no need to send mail.

请帮帮我.

推荐答案

这是一种可能的选择.以下示例可能会让您了解如何使用 Send Email task 实现通过电子邮件发送结果集.此示例展示了如何遍历查询结果集以形成随后将使用 Send Email task 通过电子邮件发送的消息正文.

Here is one possible option. Following example might give you an idea of how you can achieve emailing a result set using Send Email task. This example shows how to loop through the query result set to form the message body that will then be emailed using Send Email task.

如果不想在结果集为空时发送电子邮件，可以在循环结果集和之间的优先约束中添加一个表达式>发送电子邮件任务.

If you don't want to send e-mail when the result set is blank, you can add an Expression to the precedence constraint between Loop resultset and Send email tasks.

该示例使用 SSIS 2008 R2 和 SQL Server 2008 R2 数据库.

The example uses SSIS 2008 R2 and SQL Server 2008 R2 database.

分步过程:

使用 SQL 脚本 部分下提供的脚本创建名为 dbo.EmailData 的表.

Create a table named dbo.EmailData using the script provided under SQL Scripts section.

屏幕截图 #1 显示了示例数据，Execute SQL 任务将在此示例中查询并通过电子邮件发送该数据.

Screenshot #1 shows sample data that Execute SQL task will query and send it in an e-mail in this example.

在 SSIS 包上，创建 5 个变量，如屏幕截图 #2 所示.

On the SSIS package, create 5 variables as shown in screenshot #2.

在 SSIS 包上，放置以下任务:Execute SQL task、Foreach 循环容器、Script taskem>Foreach 循环容器和发送电子邮件任务.

On the SSIS package, place the following tasks: Execute SQL task, Foreach loop container, Script task within the Foreach loop container and Send Email task.

配置执行 SQL 任务，如屏幕截图 #3 和 #4 所示.

Configure the Execute SQL task as shown in screenshots #3 and #4.

配置 Foreach 循环容器，如屏幕截图 #5 和 #6 所示.变量映射部分显示查询结果列出现的顺序以及它们如何分配给 SSIS 变量.这些变量将用于在 Script task 中形成电子邮件消息.

Configure the Foreach loop container as shown in screenshots #5 and #6. Variable mappings section shows the order in which the query result columns appear and how they are assigned to SSIS variables. These variables will be used to form the email message inside the Script task.

在脚本任务中，将代码替换为脚本任务代码部分下显示的代码.该脚本任务具有非常简单的纯文本电子邮件格式.

In the Script task, replace the code with the one shown under the Script task code section. The script task has very simple plain text email message formatting.

配置发送电子邮件任务，如屏幕截图 #7 所示.您需要在 From 和 To 字段中使用有效的电子邮件地址对其进行配置.

Configure the Send Email task as shown in screenshot #7. You need to configure it with valid email address in From and To fields.

配置控制流任务后，您的包应如屏幕截图 #8 所示.

After configuring the Control flow tasks, your package should look like as shown in screenshot #8.

示例包执行如屏幕截图 #9 所示.

Sample package execution is shown in screenshot #9.

包裹发送的电子邮件显示在屏幕截图 #10 中.一些信息已从屏幕截图中删除.您可以将屏幕截图 #1 中显示的表格数据与此电子邮件输出进行比较，它们应该相同.

E-mail sent by the package is shown in screenshot #10. Some information have been removed from the screenshot. You can compare the table data shown in screenshot #1 with this email output and they should same.

希望有所帮助.

SQL 脚本:.

CREATE TABLE [dbo].[EmailData](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemId] [varchar](255) NOT NULL,
    [ItemName] [varchar](255) NOT NULL,
    [ItemType] [varchar](255) NOT NULL,
    [IsProcessed] [bit] NULL,
 CONSTRAINT [PK_EmailData] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

脚本任务代码:

C# 代码只能在 SSIS 2008 及更高版本 中使用..

C# code that can be used only in SSIS 2008 and above..

/*Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_7f59d09774914001b60a99a90809d5c5.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            Variables varCollection = null;
            string header = string.Empty;
            string message = string.Empty;

            Dts.VariableDispenser.LockForWrite("User::EmailMessage");
            Dts.VariableDispenser.LockForWrite("User::ItemId");
            Dts.VariableDispenser.LockForWrite("User::ItemName");
            Dts.VariableDispenser.LockForWrite("User::ItemType");
            Dts.VariableDispenser.GetVariables(ref varCollection);

            //Set the header message for the query result
            if (varCollection["User::EmailMessage"].Value == string.Empty)
            {
                header = "Execute SQL task output sent using Send Email Task in SSIS:\n\n";
                header += string.Format("{0}\t{1}\t\t\t{2}\n", "Item number", "Item name", "Item type");
                varCollection["User::EmailMessage"].Value = header;
            }

            //Format the query result with tab delimiters
            message = string.Format("{0}\t{1}\t{2}",
                                        varCollection["User::ItemId"].Value,
                                        varCollection["User::ItemName"].Value,
                                        varCollection["User::ItemType"].Value);

            varCollection["User::EmailMessage"].Value = varCollection["User::EmailMessage"].Value + message;

            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

屏幕截图 #1:

屏幕截图 #2:

屏幕截图 #3:

屏幕截图 #4:

屏幕截图 #5:

屏幕截图 #6:

屏幕截图 #7:

截图 #8:

屏幕截图 #9:

屏幕截图 #10:

这篇关于如何使用 SSIS 包从电子邮件正文中的表格发送记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！