What is Lookup?
Lookup Transformation is used for Incremental data load. Before we going to use we need to understand the Multicast Transformation.
In the below example I have demonstrated how to load the student data in the staging table by using Loopup transformation.
Where do you see the Loopup transformation.? You can drag and drop from the Common category from SSIS Tool
Double click on Lookup Transformation properties, you can see the below different types of options.
Cache Mode :
Full Cache: Generate and load the reference dataset into cache before the Lookup transformation is executed.
Partial Cache: Generate the reference dataset during the execution of the Lookup transformation. Load the rows with matching entries in the reference dataset and the rows with no matching entries in the dataset into cache.
No Cache: Generate the reference dataset during the execution of the Lookup transformation. No data is loaded into the cache.
Cache connection manager
Configure the Lookup transformation to use a Cache connection manager. This option is available only if the Full cache option is selected.
OLE DB connection manager
Configure the Lookup transformation to use an OLE DB connection manager.
Drag and drop the column which one we need to have lookup with the source table.
When you select Redirect rows to no match output, the rows are redirected to a no-match output and are not handled as errors. The Error option on the Error Output page of the Lookup Transformation Editor dialog box is not available.
We will discuss in detail in the upcoming article how we use the Loopup transformation in real scenairo.
Thank You..!
Leave a Reply