i have a table with 100mil records i have to lookup 2 columns from source with 2 columns in destination and then i have to aggregate the no match output from lookup and insert in destination. currently i am using look up which is connected to aggregate . It is very slow like 10k records every 5 min. how can improve the performance please suggest

Have you established where the bottleneck is? How many rows go in the "No match" direction? Is your Lookup using "full cache" mode? If not, then that is most likely your bottleneck.Craig Bryden - Please mark correct answers

it is using full cache mode

It takes a long time to cache all the 100 mil rows because you used the full cache. Try using partial cache option and the performance must improve. See this post on more info: http://blogs.msdn.com/b/mattm/archive/2008/10/18/lookup-cache-modes.aspxArthur My Blog

My understanding seems to be different to Arthur's. I though you had a source table of 100m rows, not a lookup that consists of 100m rows. Reddy335, please can you clarify how many rows are in the source table and how many are returned by the query that populates the lookup? Craig Bryden - Please mark correct answers

source has 100mil because it is the first time i am loading the table there will be 100mil no match output. then i have to aggregate the no match output on the 2 coulmns that i have used for lookup

Did you try the partial cache option?Arthur My Blog