In a SQL Server Integration Service (SSIS) package, when lookups are performed on the tables which are inserted in the same package by a previous task, the error “could not match” is raised.
See the failed package layout. From the insert data, two records will be inserted and at the lookup those values are used to get the id. However, the package fails indicating that records are not found even though those records are in the table.
This is happening due to the cache setting in the lookup. There are three types of cache, Full, Partial and No caching.
Full pre-caching, is when the complete reference data set is read before the input is processed. This is the default caching type.
Partial caching, is when the Lookup transformation specifies the size of the cache that is loaded with reference data. This option is available only with connections that support keyed access.
No caching, the reference data set is accessed by each row in the rowset.
As the default caching type is full, before the start of the data flow, it will cache the lookup data to improve the performance. As the data is inserted after the caching, the lookup it will failed. Therefore, for this kind of implementation you need to set caching type to No Cachin
Posts
No comments:
Post a Comment