Post reply

How to stop package if result in SQL= "x"? (New to SSIS)

  • November 10, 2009 at 9:56 am

    #206442

    I'm developing a package that includes an initial step that checks for a duplicate ID in a table.

    How do I design the package to halt (i.e., do not proceed to other steps) if the SQL query finds a duplicate key?

    I'm guessing there's a way to pass a result to a variable or to use the script object, but just not sure.

    Thanks in advance,

    Pete

  • November 10, 2009 at 10:13 am

    #1077375

    You can do this with a simple Execute SQL Task and a precedence constraint with an expression. In your Execute SQL Task, configure an output variable (under the Variable Mapping tab) and set up a variable to store the result of your query. You'll need to modify your query to include the variable, something like:

    SELECT ? = COUNT(*)

    FROM MyTable

    WHERE MyCondition = 'Something'

    Now configure a precedence constraint to your next task or container in line, and configure it to use an expression to test the value of the control variable you set in the EXecute SQL Task. If the condition is met, processing will continue, otherwise the package will cease processing at that point.

    Let me know if you need further assistance...

    hth,

    Tim

    Tim Mitchell
    TimMitchell.net | @tmitch.net | Tyleris.com
    ETL Best Practices

  • November 10, 2009 at 10:35 am

    #1077384

    Thanks, looks do-able.

    I'll give it go.

    --Pete

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

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