Lookup cachetype = none question

HI, I use a lookup transform on one of my dataflow. My data look a bit like this (the actual data is more complicated, kit is a combination of type 1 - description and type 2 - code):

ID Code Description
-- -
1 AAA PRODUCT1
2 AAA PRODUCT2
3 AAA PRODUCT3
4 BBB PRODUCT4

The problem is simple: I would like to insert ID 1 and update it with subsequent rows that have the same code.

If the lookup transform finds a match, the row is updated, else, the row is inserted (using the error path of the lookup) via an OLE_DB command. The lookup cachetype is set to none. My problem is all rows are inserted. But if I use a second lookup that gives me the ID using the Code column, the second lookup sees the inserted data.

My question is why the second lookup is able to find out the inserted data while the first one cannot? The SCD wizard cannot resolve this either. I resolved this by using an asynchronous script component and manage the logic in there. But, still, a lookup with no cache (roud trip to the DB every time) should be able to do the job.

Thank you,
Ccote

Generally this is because the dataflow works on buffers not rows. All the rows in a buffer are processed by a component before the rows are passed on to the next component. So the 1st lookup doesn't find the data because it most likely hasn't actually been inserted yet, while the 2nd one finds it because the data has actually been inserted.

HTH,

Matt