Post reply

Prevent execution of Tasks on 'row count' condition

  • October 1, 2008 at 10:23 am

    #123888

    Hi,

    I have created a SSIS package but need to include an additional object between 2 existing 'Execute SQL tasks'.

    The 1st SQL Task checks whether the row count of dbo.ABN_Extract (under condition) is greater than 0. If so, the record is deleted.

    --Checks for existing record in imported staging table and deletes ALL --records in staging table if found

    IF (SELECT COUNT(*)

    FROM dbo.abn_extract E

    JOIN dbo.ABN_File_Header H

    ON SUBSTRING(E.[Column 0], 1, 13) = H.Header_Id) > 0

    BEGIN

    DELETE FROM dbo.abn_extract

    END

    Requirement

    I need a method within the package to detect whether the row count in dbo.abn_extract is = 0. If it is, then

    1. I don't want the remaining objects/tasks to be performed

    2. To send an alert using the 'Send Mail' facility

    How do I do this?

    Thanks in advance,

    Neal

  • October 2, 2008 at 6:04 am

    #879900

    you can store the count in a variable using a execute sql task. then, set the workflow constraints to use an expression. place your check here. you can build two routes, one if count = 0 and the other if count > 0.

    you will need to be careful of checking the count and then acting on it in case rows have been added between tasks. you could use a transaction to lock the table until your necessary actions have completed.

    tom

    Life: it twists and turns like a twisty turny thing

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply