You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup transformation. The reference data to be used in the transformation changes with every package…

sp_FuzzyLookupTableMaintenanceInvoke is a CLR-based stored procedure that incrementally updates a persisted index, and it cannot be executed when CLR integration is disabled on the server.

GenerateNewIndex builds a fresh match index at the start of each package execution using the current state of the reference table, ensuring the Fuzzy Lookup always operates against up-to-date data. Because the index is not persisted to disk, the CLR-based index maintenance stored procedures are never invoked, which satisfies the constraint that CLR integration cannot be enabled. This is the most efficient configuration that is both correct and compatible with the given environment restrictions.

sp_FuzzyLookupTableMaintenanceUninstall removes existing fuzzy lookup index infrastructure from the database rather than configuring the transformation to handle changing reference data each execution.

GenerateAndPersistNewIndex saves the index to the database for reuse across executions, but maintaining this persisted index requires CLR integration, which is explicitly disabled in this scenario.