Question Regarding Known Exceptions

  • I have an SSIS 2008 package that processes text files and places the rows into a SQL Server table. I have a way to divert unexpected errors ("bad rows") and I also have a way to process known errors, which we call "known exceptions." However, I'm not quite sure how to do in SSIS what I need to do. Here's the gist. WHERE UPC LIKE '4000%' AND QtyOnHand < 20, then the text file row should be diverted to the known exceptions table.

    I don't think a Lookup transform will do the trick here, so I am considering the Conditional Split transform. But I don't know how to make those conditions similar to the Where clause above work in the Conditional Split. Any clues? Thank you in advance.

  • The Conditional Split will do the job, you could add a condition named known exceptions with its condition like SUBSTRING(UPC,1,4) == "4000" && QtyOnHand < 20

    Cheers,

    Hope this helps,
    Rock from VbCity

  • I tried the following and it didn't work. What did I do wrong?

    SUBSTRING(Upc,1,4) == "4000" && QtyOnHand < 20 && QtySold == 0 && QtyReturned == 0 && TradingPartnerId == "4159772900"

  • The expression looks fine. Either it's not mapped to a second stream or something else went funky.

    Drop a dataviewer onto the stream it's NOT supposed to be in and doublecheck your data meets all of those rules. NULL fails an ==0 check, you're restricted to a single value for another component.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • You are testing with a very specific record, as Evil Kraig suggested, add a data viewer, plus add one condition at a time to the filter, I will start with TradingPartnerId == "4159772900" followed by SUBSTRING(Upc,1,4) == "4000" until you hit the offending condition item.

    Your condition is fine, your data might not satisfy the condition.

    Hope this helps,
    Rock from VbCity

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

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