February 23, 2012 at 11:05 am
I'm working on some expense report automation. In the process of handling expense reports, I need to check and determine if the accounts that are being posted to actually exist. In the event that an account does not exist, the entire process must fail and rollback. That's all fine and dandy.
The problem is - I need those account numbers. I need to report on which account numbers need to be created so that accounting can make them. I need to log them to a table so that Report Server can continuously send them to accounting for as long as they don't exist.
So, in short, I need to redirect rows from a lookup failure, but still fail. Is this possible.
I suppose I could handle an onError event and redo all the lookup logic and redirect the rows, but that's redundant. I hate redundant program logic for some very valid reasons. I know SSIS isn't exactly the pinnacle of redundancy elimination, but if there is a better way, I'd like to use it.
Does such a "better way" exist?
February 24, 2012 at 12:13 am
You have a bit of conflicting requirements 🙂
Rollback if there's a failure, but still insert some rows somewhere.
If you could do everything in TQSL you could maybe do a TRY ... CATCH?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 24, 2012 at 7:23 am
That's a fair point. I hadn't considered that.
I've actually revised the process such that the entire transaction occurs after the lookup. I still need to perform the lookup to check for that single type of error, and report on it if it exists. I still need a transaction, however, for the overall insert, in case it fails in some other way.
So I need something similar but not exactly the same. I need the next step to not be executed in the event that the lookup "fails", but I still need lookup failure to redirect all failed rows into a table. I suppose this could be done through a script task setting a variable, and using an expression on the precendance constraint for the next task. This will work because I don't actually need the task to fail. In fact, I don't actually want the process to fail because it will notify me in the event of process failure. I don't really care about this specific type of failure, I just want to make sure Accounting is notified. In the event of another type of error, I definitely want to be notified.
February 24, 2012 at 11:25 am
If you don't want it to actually fail if the lookup doesdn't work, then use the "redirect row to no match output" option, rather than the "redirect rows to error output". Once you enable that, you can then set up a whole separate workflow for the lookup failure (including sending a notification that someone needs to do something with the lookup failures).
This is one of the options under the lookup transformation editor.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply