An SSIS package keeps failing for no reason. There are only three lookup transform components in the package (besides the source and output component), two running in partial cache mode and the other in full cache mode. Each lookup will need to find records among tens of millions of entries.

When the full-cached component have cached around 10M records the data flow will fail. The only error message is "Data flow task failed" (no error code provided). At that time the server has 15% free memory, and SSIS uses up about 3GB. There is no warning about memory space during execution. Windows Server and SQL Server are 64 bit, but I'm not sure about Visual Studio BIDS.


1 Answer 1


Think about how the reference and input data sets are configured. The reference data set (smallest set) should be the only cached data set, while the input rows(i.e. the 10M) pass through that execution tree's buffer.

Also, running parallel lookups on different subsets of the data and aggregating all outputs with a UNION ALL at the end might yield better performance, but test it.

Lasty, look for any block transformations (i.e. Sort, Aggregates, etc.) before or after the lookup.

Getting Optimal Performance with Integration Services Lookups

Integration Services: Performance Tuning Techniques

Not the answer you're looking for? Browse other questions tagged or ask your own question.