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 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply