SSIS fail out of Foreach loop to stop processing

  • Hello experts,

    I'm working on importing flat files into a database. I have used a Foreach loop in an SSIS project in SQL Server Data Tools for SQL 2012. have just started developing these packages so please let me know of any rookie mistakes I need to correct.

    If even one of the files has not been updated in the last day, I want the processing to stop and an alert email sent out.

    Schematically, the control flow I need is as follows:

    [Foreach loop] ---> Failure ----> Send import failure email

    |

    |

    Success

    [Proceed with import]

    I have tested with setting one of the files to be too old, on purpose, to trigger the failure. What happens now, though, is that I get the Failure email but I also get a SQL error from a task in the "Proceed with import" section, as if the Success path has also been followed.

    In both success and failure cases, I have tried to set Precedence Constraints with "Expression and Constraint" as the Evaluation operation, with a Boolen value set for the expressions. @[User::ProcessFile]= False or @[User::ProcessFile]= True depending on the case.

    Could someone explain (or direct me to a reference explaining) how to make sure the SSIS package is following the path I expect it to?

    Thanks for any help!

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • There's no way to cause a Foreach loop to stop in the middle unless you deliberately fail it out. If you want to do that, stick a script task in there to verify the file changes (or lack thereof) and then set the result of the Foreach container to be a failure. Of course, this will cause your entire package to fail, which is not ideal.

    A better solution is to not use a Foreach loop as your file check and instead use a script task to check the files for changes. Then use split precendence to either send any changes into a Foreach loop container or to send to an email task that says "no files today."

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Thanks for that advice - I'll definitely check it out.

    In the meantime, for this problem, I eventually found out what was happening.

    I was using @[User::Variable] = 0 instead of the operator to check for equality @[User::Variable] == 0. Once I fixed that the branching worked. So it was just my beginner's ignorance in this case. :blush:

    Thanks again!

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • webrunner (2/12/2016)


    I eventually found out what was happening.

    I was using @[User::Variable] = 0 instead of the operator to check for equality @[User::Variable] == 0. Once I fixed that the branching worked. So it was just my beginner's ignorance in this case. :blush:

    Oh, dear. I hate to break it to you, but that's not "beginner's ignorance." Even well-seasoned DBAs make THAT mistake... More often then we like to admit. :w00t:

    But I'm glad you figured it out.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin (2/12/2016)


    webrunner (2/12/2016)


    I eventually found out what was happening.

    I was using @[User::Variable] = 0 instead of the operator to check for equality @[User::Variable] == 0. Once I fixed that the branching worked. So it was just my beginner's ignorance in this case. :blush:

    Oh, dear. I hate to break it to you, but that's not "beginner's ignorance." Even well-seasoned DBAs make THAT mistake... More often then we like to admit. :w00t:

    But I'm glad you figured it out.

    Haha, thanks, that makes me feel better! 🙂

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 5 posts - 1 through 4 (of 4 total)

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