SQL 仅当给定查询不存在记录时，如何继续执行下一个任务？

How to proceed to the next task only if no records exist for a given query?

提问by Vince Ashby-Smith

I have the following piece of SQL that will check if any duplicate records exist. How can I check to see if no records are returned? I'm using this in an SSIS package. I only want it to proceed to the next step if no records exist, otherwise error.

我有以下一段 SQL 将检查是否存在任何重复记录。如何检查是否没有返回记录？我在 SSIS 包中使用它。如果没有记录存在，我只希望它继续下一步，否则会出错。

SELECT      Number
        ,   COUNT(Number) AS DuplicateCheckresult
FROM        [TelephoneNumberManagement].[dbo].[Number]
GROUP BY    Number
HAVING      COUNT(Number) > 1

回答by

Following example created using SSIS 2008 R2and SQL Server 2008 R2backend illustrates how you can achieve your requirement in an SSIS package.

以下使用SSIS 2008 R2和SQL Server 2008 R2后端创建的示例说明了如何在 SSIS 包中实现您的要求。

Create a table named dbo.Phoneand populate it couple records that would return duplicate results.

创建一个名为的表dbo.Phone并填充它，将返回重复结果的几条记录。

CREATE TABLE [dbo].[Phone](
    [Number] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO dbo.Phone (Number) VALUES 
    (1234567890),
    (1234567890);
GO

You need to slightly modify your query so that it returns the total number of duplicates instead of the duplicate rows. This query will result only one value (scalar value) which could be either zero or non-zero value depending on if duplicates are found or not. This is the query we will use in the SSIS package's Execute SQL Task.

您需要稍微修改您的查询，以便它返回重复的总数而不是重复的行。此查询将仅产生一个值（标量值），该值可能为零或非零值，具体取决于是否找到重复项。这是我们将在 SSIS 包的执行 SQL 任务中使用的查询。

SELECT COUNT(Number) AS Duplicates
FROM
(   
    SELECT      Number
            ,   COUNT(Number) AS NumberCount
    FROM        dbo.Phone
    GROUP BY    Number
    HAVING      COUNT(Number) > 1
) T1 

On the SSIS package, create a variable named DuplicatesCountof data type Int32.

在 SSIS 包上，创建一个名为Int32DuplicatesCount数据类型的变量。

On the SSIS package, create an OLE DB Connection manager to connect to the SQL Server database. I have named it as SQLServer.

在 SSIS 包上，创建一个 OLE DB 连接管理器以连接到 SQL Server 数据库。我将其命名为SQLServer。

On the Control Flow tab of the SSIS, package, place an Execute SQL Task and configure it as shown below in the screenshots. The task should accept a single row value and assign it to the newly create variable. Set the ResultSetto Single row. Set the Connection to SQLServerand the SQLStatement to SELECT COUNT(Number) AS Duplicates FROM (SELECT Number, COUNT(Number) AS NumberCount FROM dbo.Phone GROUP BY Number HAVING COUNT(Number) > 1) T1.

在 SSIS 的 Control Flow 选项卡上，打包，放置一个 Execute SQL Task 并按照屏幕截图所示进行配置。该任务应接受单行值并将其分配给新创建的变量。将 设置ResultSet为单行。将 Connection 设置为SQLServer并将 SQLStatement 设置为SELECT COUNT(Number) AS Duplicates FROM (SELECT Number, COUNT(Number) AS NumberCount FROM dbo.Phone GROUP BY Number HAVING COUNT(Number) > 1) T1。

On the Result Set section, click on the Addbutton and set the Result Name to 0. Assign the variable User::DuplicatesCountto the result name. Then click OK.

在结果集部分，单击添加按钮并将结果名称设置为0。将变量分配User::DuplicatesCount给结果名称。然后单击确定。

Place another task after the Execute SQL Task. I have chosen Foreach Loop Container for sample. Connect the tasks as shown below.

在执行 SQL 任务之后放置另一个任务。我选择了 Foreach Loop Container 作为示例。如下图所示连接任务。

Now, the requirement is if there are no duplicates, which means if the output value of the query in the Execute SQL task is zero, then the package should proceed to Foreach loop container. Otherwise, the package should not proceed to Foreach loop container. To achieve this, we need to add a expression to the precedence constraint (the green arrow between the tasks).

现在，要求是如果没有重复项，这意味着如果执行 SQL 任务中查询的输出值为零，则该包应继续执行 Foreach 循环容器。否则，包不应进入 Foreach 循环容器。为此，我们需要向优先约束（任务之间的绿色箭头）添加一个表达式。

Right-click on the precedence constraint and select Edit...

右键单击优先约束并选择 Edit...

On the Precedence constraint editor, select Expressionfrom the Evaluation operation dropdown. Set the expression to @[User::DuplicatesCount] == 0in order to check that the variable DuplicatesCountcontains the value zero. Value zero means that there were no duplicates in the table dbo.Phone. Test the expression to verify that the syntax is correct. Click OK to close the verification message. Click OK to close the precedence constraint.

在优先约束编辑器上，Expression从评估操作下拉列表中进行选择。将表达式设置@[User::DuplicatesCount] == 0为以检查变量是否DuplicatesCount包含值零。零值表示表中没有重复项dbo.Phone。测试表达式以验证语法是否正确。单击“确定”关闭验证消息。单击确定关闭优先约束。

Now, the Control Flow should look like this. The precedence constraint will be denote with fx, which represents there is a constraint/expression in place.

现在，控制流应该是这样的。优先约束将用fx表示，表示存在约束/表达式。

Let's check the rows in the table dbo.Phone. As you see, the value 1234567890exists twice. It means that there are duplicate rows and the Foreach loop container shouldn't execute.

让我们检查表中的行dbo.Phone。如您所见，该值1234567890存在两次。这意味着存在重复的行并且不应执行 Foreach 循环容器。

Let's execute the package. You can notice that the Execute SQL Task executed successfully but it didn't proceed to Foreach Loop container. That's because the variable DuplicatesCount contains a value of 1 and we had written a condition to check that the value should be zero to proceed to Foreach loop container.

让我们执行包。您可以注意到 Execute SQL Task 已成功执行，但没有继续执行 Foreach Loop 容器。这是因为变量 DuplicatesCount 包含的值为 1，并且我们编写了一个条件来检查该值是否应为零以继续执行 Foreach 循环容器。

Let's delete the rows from the table dbo.Phone and populate it with non-duplicate rows using the following script.

让我们从表 dbo.Phone 中删除行，并使用以下脚本用非重复行填充它。

TRUNCATE TABLE dbo.Phone;

INSERT INTO dbo.Phone (Number) VALUES 
    (1234567890),
    (0987654321);

Now, the data in the table is as shown below.

现在，表中的数据如下所示。

If we execute the package, it will proceed to the Foreach Loop container because there are no duplicate rows in the table dbo.Phone

如果我们执行该包，它将继续执行 Foreach Loop 容器，因为表中没有重复的行 dbo.Phone

Hope that helps.

希望有帮助。

回答by Joel Brown

What you need to do to is work with @@ROWCOUNT, but how you do it depends on your data flows. Have a look at this discussion, which points out how to do it with either one or with two data flows.

您需要做的是使用@@ROWCOUNT，但如何做取决于您的数据流。看看这个讨论，它指出了如何使用一个或两个数据流来做到这一点。

Using Row Count In SSIS

在 SSIS 中使用行计数