Hi All, Please help me to check on this fuzzy lookup issue. [Situation] I have selected to store and maintain index for first time, it works fine (just input 4 variables and look-up from table which contains 1 million records). After execution, I altered the option to use existing index to do the same thing but receive the error msg. [Environment] SQL Server 2012 RC0 env. & SSDT development env. [Error Msg] [Fuzzy Lookup [111]] Warning: Catastrophic failure [SSIS.Pipeline] Error: Fuzzy Lookup failed the pre-execute phase and returned error code 0x8000FFFF. I try to reduce my look-up table into 500,000 record, and it works fine by using existing index, but I don't know why??... Should I change any settings about memory allocation(I set MaxMemoryUsage to 0)? Or may be about RC0 issue? I have changed WarmCaches property to False but not works.... could anyone give some suggestions? Thanks in advanced. BR, Jack

Hi, I believe RC is not supported anymore as RTM is out. Please try it with RTM version and if the problem is still exists you should post your DDL, and teh SSIS dtsx xml for us to test it. I hope it helps. JanosThere are 10 type of people. Those who understand binary and those who do not. My Blog

Hi Janos, Thanks for your suggestion, after installed the 2012 RTM version, the package still go wrong with the same error message while using existing index. And I also found the weird thing when I try to use the drop down list to choose the index which created before, the list always show "Loading...", does anyone could tell me where is the problem here? Thanks in advanced. BR, Jack

Hi Jack, When you select Maintain stored index on the Reference Table tab of the Fuzzy Lookup Transformation Editor, the transformation uses managed stored procedures to maintain the index. These managed stored procedures use the common language runtime (CLR) integration feature in SQL Server. By default, CLR integration in SQL Server is not enabled. To use the Maintain stored index functionality, you must enable CLR integration. For more information, see Enabling CLR Integration. Because the Maintain stored index option requires CLR integration, this feature works only when you select a reference table on an instance of SQL Server where CLR integration is enabled. For more information about Fuzzy Lookup Transformation, please refer to: http://technet.microsoft.com/en-us/library/ms137786.aspx Thanks, Eileen

Hi Eileen, Thanks for your information, about enable CLR Integration, I have already set it on as below. BR, Jack

Finally I solved this issue by clicking some options of the Comparison Flags in Fuzzy Lookup component. Just for your reference, http://msdn.microsoft.com/en-us/library/ms141038 Thanks for all your help. Jack

Hi Jack, Thank you for sharing your solutions and experience here. It will be very beneficial for other community members who have similar questions. Thanks, Eileen