GenerateAndPersistNewIndex causing "Not enough storage is available" error

Here is a good “gotcha” for the GenerateAndPersistNewIndex setting in the Fuzzy Lookup component delivered with SSIS and SQL 2005.

Problem: MaxMemoryUsage set to 0 (Unlimited) - When this is set to 0, and the MatchIndexOpetion set to GenerateAndPersistNewIndex, the package will fail stating: "Not enough storage is available to complete this operation"

Fix: Set MaxMemoryUsage to 256

There are a few things really odd things that I've discovered:

· The description of MaxMemoryUsage from MS says it applies to the transformation process, not the index creation process.

· With MaxMemoryUsage set to 0 and plenty of RAM, the package may still fail even though it isnt close to maxing out the RAM.

· Even with MaxMemoryUsage set to 256, it is going past this memory requirement. (although I can see it is using a lot less memory than with a setting of 0)