Roadmap › SSIS — SQL Server Integration Services › Module: Data Flow
Lessons in this course (13)
7. Lookup transformation and cache modes
Intermediate★ Must knowLesson 7 of 13
Lookup matches each input row against a reference dataset (e.g. get CustomerKey from DimCustomer by CustomerId).
| Cache mode | How | When |
|---|---|---|
| Full cache (default) | Loads the entire reference query into memory before rows flow | Reference fits in memory — fastest |
| Partial cache | Queries DB on cache miss, caches results | Large reference, few distinct keys used |
| No cache | Queries DB for every row | Reference changes during the load; tiny inputs |
| Cache Connection Manager | Cache built once (Cache Transform), reused by several lookups/packages | Same reference used many times |
Outputs: Match, No Match (configure "Redirect rows to no match output" — e.g. to insert new dimension members), Error. Lookups are case-sensitive and type-sensitive in full cache mode — trim/upper both sides. Use a SQL query selecting only key + needed columns.