SQL Server Integration Services (SSIS) Lookup does not like large data from SQL Server 2000

If you are using the Lookup component of SSIS inside a package and your lookup is really big (let's say 400,000 rows) and this lookup comes from an SQL Server 2000 table, you will have the strange effect that Lookup will report all rows as "not found".

All rows are then send to the error output so the data flow will look like this:

I have seen this behavior only if the data comes from SQL Server 2000, when using data from an SQL Server 2005 the component worked just fine.

The solution is simple: Just limit the cache size on the Lookup component and everything will work.