如何使用 SSIS 将查询的结果集作为电子邮件中的附件发送? [英] How do I send the result set from a query as an attachment in an email using SSIS?
问题描述
我有一个来自 Execute SQL Task 查询的结果集保存在 System.Object 变量中，我想使用 Send Mail Task 使用MessageSource 上的以下表达式.
I have a result set from an Execute SQL Task query saved on a System.Object variable and i would like to send this results using a Send Mail Task using the following expression on the MessageSource.
"请查找附件数据摘要\n\n" + SUBSTRING( @[User::myVariable] ,1,3990)
+ "\n\n"
"Please find attached data summary\n\n" + SUBSTRING( @[User::myVariable] ,1,3990)
+ "\n\n"
推荐答案
现在您可能已经找到了问题的答案.这个答案适用于可能偶然发现这个问题的其他人.我认为您不能在表达式中使用对象变量.您需要遍历查询结果对象并将其格式化为字符串，以便您可以在电子邮件消息中发送查询输出.您还可以将数据导出到文件并将文件作为附件发送.这是另一种可能的选择.此示例展示了如何遍历查询结果集以形成消息正文，然后使用发送电子邮件"任务通过电子邮件发送该消息正文.
Probably, you have found an answer to your question by now. This answer is for others who might stumble upon this question. I don't think you can use object variable in expression. You need to loop through the query result object and format it to a string so that you can send the query output in an e-mail message. You can also export the data to a file and send the file as an attachment. That is another possible option. 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.
分步过程:
使用 SQL 脚本 部分下提供的脚本创建名为
dbo.EmailData的表.
Create a table named
dbo.EmailDatausing 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 将查询的结果集作为电子邮件中的附件发送?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
