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

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.

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

Following example created using SSIS 2008 R2 and SQL Server 2008 R2 backend illustrates how you can achieve your requirement in an SSIS package.

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

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.

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 DuplicatesCount of data type Int32.

On the SSIS package, create an OLE DB Connection manager to connect to the SQL Server database. I have named it as 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 ResultSet to Single row. Set the Connection to SQLServer and 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.

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

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

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).

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

On the Precedence constraint editor, select Expression from the Evaluation operation dropdown. Set the expression to @[User::DuplicatesCount] == 0 in order to check that the variable DuplicatesCount contains 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.

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.

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

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.

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

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

Hope that helps.

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.

Using Row Count In SSIS