RSS Feeds

FAQ Topics

All FAQ's

General DBA

General Developer

DBA Performance Tuning

Developer Performance Tuning

Clustering

Error Messages

USEFUL SITES :

ASP.NET Tutorials
Windows and SQL Azure Tutorials
Cloud Hosting Magazine
SharePoint Tutorials
Windows Server Help

Write for Us

Share your SQL Server knowledge with others and raise your profile in the community More...

How to Perform Multiple lookups to the same table

In a SQL Server Integration Service (SSIS) package, when lookups are performed on tables inserted in the same package by a previous task, the error “could not match” is raised.

Above is 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 occurs due to the cache setting in the lookup. There are three types of cache, Full, Partial and No caching.

Full pre-caching - when the complete reference data set is read before the input is processed. This is the default caching type.

Partial caching - 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 performance. As the data is inserted after the caching, the lookup will fail. Therefore, for this kind of implementation the caching type needs to be set to No Caching.