如何根据查询返回的记录数控制 SSIS 包流? [英] How to control SSIS package flow based on record count returned by a query?

问题描述

在执行我的包之前，我试图首先检查是否有任何新记录要处理.我在 SQL Server 2008 R2 表中有一个名为已处理"的位字段，如果已处理，则值为 1，否则为 0.

I'm trying to first check if there are any new records to process before I execute my package. I have a bit field called "processed" in a SQL Server 2008 R2 table that has a value of 1 if processed and 0 if not.

我想这样查询:

select count(processed) from dbo.AR_Sale where processed = 0

如果结果为 0，我想发送一封电子邮件，说明记录不存在.如果大于零，我想继续执行包.我是 SSIS 的新手，似乎无法弄清楚为此使用什么工具.

If the result is 0 I want to send an e-mail saying the records are not there. If greater than zero, I want to proceed with package execution. I am new to SSIS and can't seem to figure out what tool to use for this.

我的包有一个数据流项，其中包含一个到数据库的 OLE DB 连接.连接使用查询返回记录.不幸的是，即使没有要处理的记录，查询也会成功完成(应该如此).这是查询:

My package has a data flow item with an OLE DB connection inside it to the database. The connection uses a query to return the records. Unfortunately, the query completes successfully (as it should) even if there are no records to process. Here is the query:

Select * from dbo.AR_Sale where processed = 0

我将这些记录复制到数据仓库，然后运行另一个查询以通过将处理的字段从 0 更改为 1 来更新源表.
任何帮助将不胜感激.

I copy these records to a data warehouse and then run another query to update the source table by changing the processed field from 0 to 1.
Any help would be greatly appreciated.

推荐答案

一种选择是结合使用优先约束和执行 SQL 任务来实现此功能.以下是如何在 SSIS 2008 R2 中实现此目的的示例.

One option would be to make use of precedence constraint in conjunction with Execute SQL task to achieve this functionality. Here is an example of how to achieve this in SSIS 2008 R2.

我根据问题中提供的信息创建了一个简单的表格.

I created a simple table based on the information provided in the question.

创建表格脚本:

CREATE TABLE dbo.AR_Sale(
    Id int NOT NULL IDENTITY PRIMARY KEY,
    Item varchar(30) NOT NULL,
    Price numeric(10, 2) NOT NULL,
    Processed bit NOT NULL
) 
GO

然后用一些示例数据填充新表.您可以看到其中一行的 Processed 标志设置为零.

Then populated the new table with some sample data. You can see that one of the row has Processed flag set to zero.

填充表格脚本:

INSERT INTO dbo.AR_Sale (Item, Price, Processed) VALUES
    ('Item 1', 23.84, 1),
    ('Item 2', 72.19, 0),
    ('Item 3', 45.73, 1);

在 SSIS 包上，创建以下两个变量.

On the SSIS package, create the following two variables.

• Processed 数据类型 Int32
• SQLFetchCount 数据类型 String 的值设置为 SELECT COUNT(Id) ProcessedCount FROM dbo.AR_Sale WHERE Processed = 0

• Processed of data type Int32
• SQLFetchCount of data type String with value set to SELECT COUNT(Id) ProcessedCount FROM dbo.AR_Sale WHERE Processed = 0

在 SSIS 项目上，创建一个指向您选择的数据库的 OLE DB 数据源.将数据源添加到包的连接管理器.在本例中，我使用将数据源命名为 Practice.

On the SSIS project, create a OLE DB data source that points to the database of your choice. Add the data source to the package's connection manager. In this example, I have used named the data source as Practice.

在包的Control Flow 选项卡上，从工具箱中拖放执行 SQL 任务.

On the package's Control Flow tab, drag and drop Execute SQL Task from the toolbox.

配置执行SQL任务的General页面如下图:

Configure the General page of the Execute SQL Task as shown below:

• 给一个合适的名称，比如检查预执行
• 将ResultSet改为Single row，因为查询返回的是一个标量值
• 设置Connection到OLE DB数据源，在这个例子中Practice
• 将 SQLSourceType 设置为 Variable 因为我们将使用存储在变量中的查询
• 将 SourceVariable 设置为 User::SQLFetchCount
• 点击左侧的Result Set页面

• Give a proper Name, say Check pre-execution
• Change ResultSet to Single row because the query returns a scalar value
• Set the Connection to the OLE DB datasource, in this example Practice
• Set the SQLSourceType to Variable because we will use the query stored in the variable
• Set the SourceVariable to User::SQLFetchCount
• Click Result Set page on the left section

配置执行SQL任务的Result Set页面如下图:

Configure the Result Set page of the Execute SQL Task as shown below:

• 单击添加"按钮添加一个新变量，该变量将存储查询返回的计数值
• 将Result Name改为0，表示查询返回的第一列值
• 将变量名设置为User::Processed
• 点击确定

• Click Add button to add a new variable which will store the count value returned by the query
• Change the Result Name to 0 to indicate the first column value returned by query
• Set the Variable Name to User::Processed
• Click OK

在包的Control Flow 选项卡上，从工具箱中拖放发送邮件任务"和数据流任务".控制流"选项卡应如下所示:

On the package's Control Flow tab, drag and drop Send Mail Task and Data Flow Task from the toolbox. The Control Flow tab should look something like this:

右键单击连接执行 SQL 任务和发送邮件任务的绿色箭头.单击编辑...绿色箭头称为优先约束.

Right-click on the green arrow that joins the Execute SQL task and Send Mail Task. Click Edit... the Green Arrow is called as Precedence Constraint.

在优先约束编辑器上，执行以下步骤:

On the Precedence Constraint Editor, perform the following steps:

• 设置求值操作为Expression
• 将表达式设置为@[User::Processed] == 0.这意味着仅当变量 Processed 设置为零时才采用此路径.
• 点击确定

• Set Evaluation operation to Expression
• Set the Expression to @[User::Processed] == 0. It means that take this path only when the variable Processed is set to zero.
• Click OK

右键单击连接执行 SQL 任务和数据流任务的绿色箭头.单击 Edit... 在 Precedence Constraint Editor 上，执行以下步骤:

Right-click on the green arrow that joins the Execute SQL task and Data Flow Task. Click Edit... On the Precedence Constraint Editor, perform the following steps:

• 设置求值操作为Expression
• 将表达式设置为@[User::Processed] != 0.这意味着仅当变量 Processed 未设置为零时才采用此路径.
• 点击确定

• Set Evaluation operation to Expression
• Set the Expression to @[User::Processed] != 0. It means that take this path only when the variable Processed is not set to zero.
• Click OK

控制流选项卡看起来像这样.您可以根据需要配置发送邮件任务来发送电子邮件和数据流任务来更新数据.

Control flow tab would look like this. You can configure the Send Mail Task to send email and the Data Flow Task to update the data according to your requirements.

当我根据填充表脚本使用数据集执行包时，包将执行数据流任务，因为有一行未处理.

When I execute the package with the data set to based on the populate table script, the package will execute the Data Flow Task because there is one row that is not processed.

当我使用脚本 UPDATE dbo.AR_Sale SET Processed = 1 在表中的所有行上将 Processed 标志设置为 1 后执行包时，包将执行发送邮件任务.

When I execute the package after setting Processed flag to 1 on all the rows in the table using the script UPDATE dbo.AR_Sale SET Processed = 1, the package will execute the Send Mail Task.

希望有所帮助.

这篇关于如何根据查询返回的记录数控制 SSIS 包流?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！