December 6, 2009 at 2:04 am
After a Lookup component, I add the fields for the dummy record on the No Match stream and then join it back to the main stream with the Merge component. However, the output rows of the merge component is equal to the Matched row count. I have verified that the rows that never reached the destination are from the No Match stream. I am sorting on the source SQL query and numbered the SortKey on the appropriate columns. Using SQL Server 2008 SP1.
What is causing the merge component not to merge my data?
Thanks!
December 6, 2009 at 7:08 am
Hi Mike
The merge component is not that good in handling rows with same key values, [highlight=#ffff11]so having NON UNIQUE KEYS[/highlight]. Your data contains NON UNIQUE KEY values, in the sorted columns.
One thing to consider is do you need the MERGE TRANSFORMATION component at that stage. Better is, to use the UNION ALL transformation.
Second sorting data reduces performance, if you can avoid it, do not use it.
Regards Kees
December 6, 2009 at 3:00 pm
I am sorting on the source table's keys so it can't be non-unique records that is causing this, but thanks for the Union All tip. The CPU usage went from 100% to barely anything.
Mike
Edit: BTW, the missing rows are also coming through so it's all good. Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply