1 / 9

Lookup Transform

Lookup Transform. SQL Server Integration Services 2008 &2012 www.sqljunkieshare.com. What does Lookup Transform do?. The Lookup transformation performs lookups by joining data in input columns with columns in a reference dataset .

lilika
Download Presentation

Lookup Transform

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Lookup Transform SQL Server Integration Services 2008 &2012 www.sqljunkieshare.com

  2. What does Lookup Transform do? • The Lookup transformation performs lookups by joining data in input columns with columns in a reference dataset. • The Lookup transformation supports the following database providers for the OLE DB connection manager: • SQL Server • Oracle • DB2 • It performs the Equi-join between source and reference dataset

  3. Properties of Lookup transform • Equi-join between source or transformation input and reference dataset • I.e at least one row should match between source and referenced dataset •  The transformation supports join columns with any data type, except for DT_R4, DT_R8, DT_TEXT, DT_NTEXT, or DT_IMAGE • Lookup Transform is case sensitive

  4. What are different cache modes in Lookup transform ? • Full cache mode • Partial cache mode • No cache mode

  5. Full cache mode • Referenced data set is just queried once during pre execution phase • All the referenced dataset is loaded in to the memory before even loading the source data into the buffer • Fast, Less query’s to the reference dataset • It takes quiet bit of time depending up on the reference data set for package execution to begin

  6. Partial cache mode • Here lookup cache begin with empty when package execution begin, when a new row comes from source to lookup the transformation then query’s the reference dataset. • If matched, that row from the reference dataset is cached in memory for next time. • If not matched it will discard the row from memory or can saved in the miss cache buffer, by default row is dropped.

  7. Continued.. • Size limit for partial cache can be set in the advanced tab of lookup transform • Once cache is fill, lookup transform drops the last referenced rows from the cache cache

  8. No cache mode • As the name implies, in this mode the lookup transform doesn't maintain a lookup cache but, it keeps the last match around, as the memory has already been allocated. this means that look up transform still query’s the database for every row.

  9. Types of connection managers? • Cache connection manager • Oledb connection manager

More Related