September 7, 2010 at 9:21 am
Hi all.
I have inherited a package that is failing on far too many occasions. I am pretty new to SSIS so please forgive me if I am asking something really daft.
The current situation
Simplifying it a bit to remove bits that I know work ok, the Data flow task loads up data from flat file source, performs a whole series of lookup transforms, all with error output configured to ignore error and NoMatchBehavior of transfrom set to "Treat rows with no matching entries as errors" , then multicasts the eventual output to a) SQL table which allows null on the looked up fields and b) a conditional split which identifies any lookup failures and logs details on rows that failed lookup to an audit table.
The problem is the data flow task often fails.
My thinking
I think it might be because the MaximumErrorCount of the Data Flow Task is set to 1. I am guessing that when any of the lookups fail to get a match with these settings, although it ignores the error and continues, it is still counted as an error and so it is incrementing the error count for the whole dataflow task? This in turn is causing it to breach the MaximumErrorCount setting and fail the overall Data Flow Task?
I cant change the MaximumError count of the Data Flow Task as I need it at 1 for other bits of the process where errors must fail the package.
My guess is that I need to alter lookup transform to use the No Match Output, and maybe then union the lookup failures back in with the Match Output so that both valid lookups and failures go forward to the multicast that can then insert the results and identify the failures and log them appropriately.
Any advice greatly appreciated. Thanks.
September 8, 2010 at 1:50 am
Is it possible to set the NoMatchBehaviour to Redirect Rows to No Match Output?
This way you have your lookup failures in a seperate flow and there are no errors cause by the lookup component when there is no match found.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 8, 2010 at 3:01 am
Its possible though more longwinded as there are 10 or so lookups done and the way the original designer has done it, he does all the lookups and then strips out any lookup failures in any of the lookups in one step rather than having a No Match output on each lookup.
I might need to resort to doing it that way though. Thanks.
September 8, 2010 at 4:05 am
MRNev (9/8/2010)
Its possible though more longwinded as there are 10 or so lookups done and the way the original designer has done it, he does all the lookups and then strips out any lookup failures in any of the lookups in one step rather than having a No Match output on each lookup.I might need to resort to doing it that way though. Thanks.
Ask yourself the following question:
are you willing to go through a lot of trouble to keep working with a flawed design
or
are you willing to go through a lot of trouble to get the package right so that no errors occur
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 8, 2010 at 4:32 am
Exactly, and that is a no-brainer, I have to sort the design to get this working properly otherwise I spend way too much time supporting users when the process fails.
Thanks for your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply