Advanced Lookup Transform
The "Advanced Lookup Transform" allows you to store a temporary cache of rows and perform lookups on that cache to pull that data into a data flow. There are two modes for the advanced lookup: Connected and Disconnected. See the Advanced Lookup Cache Connection Manager page to learn more about setting up the connection manager.
Table of Contents
• Select Cache Connection Manager - Select an Advanced Lookup Cache Connection Manager. If you have not created one yet, you will need to create one. Once you have one selected, it will automatically populate the available columns.
• Map Input Columns To Cache Input Parameters - This is where you will map input columns to the input parameters defined in the "Advanced Lookup Cache Connection Manager".
• What Should Happen To Non-Matched Rows - This tells the component what should happen when a non-matched row is found in the lookup.
o Fail Component - This option will fail the component when a non-matched row is found.
o Redirect Rows To Non-Matched Output - This option will redirect the rows to the non-matched output
o Output Null Values To Output - This option will output NULL values for each of the output columns defined in the advanced lookup
• Output Columns (Defined in Cache Connection Manager) - This section lists all of the output columns defined in the "Advanced Lookup Cache Connection Manager". You have a few choices on how to output the values of the output columns based on the column action.
o Add New Output - This option will create a new output column based on the column alias
o Replace Input Column - This option will allow you to overwrite the current value of the selected input column with the value of the output column from the lookup cache.
• Refresh Columns From Cache Button - This button will refresh the columns from the cache connection manager.
Setting up an Advanced Lookup Transform
1. Create a data flow task, then add an OLE DB Source. In this example we are using an OLEDB source connected to AdventureWorksDW. (download here)
2. For this example select only the following columns from the columns tab.
3. Now, create a lookup cache using the "Advanced Lookup Cache Transform". Drag an instance of "TF Advanced Lookup Cache Transform" to the designer window. Attach the OLE DB Source Output to the "TF Advanced Lookup Cache Transform". Now open the transform by double clicking it, which will open the edit window.
4. Click on the "Choose Cache Connection Manager" drop down and select "Create New Lookup Cache Connection..."
5. Setup the "Advanced Lookup Cache Connection Manager". Then deselect all output except for "Product Key".
6. Next, click on the "Input Parameters" tab and create two input parameters named "ProductAlternateKey" and "OrderDate".
7. Then, create the following conditions: Note that "Is Blank" = "Is Null"
8. Click "OK" and then your window should look like this:
9. Add a new data flow task and a new OLE DB source, for this example we are connecting to AdventureWorks and creating a custom SQL query.
10. Add a new TF Advanced Lookup Transform to the designer, drag the output from the OLE DB source to the TF Advanced Lookup Transform. Then open of the transform by double clicking it. Once the editor window is open. Choose the previously created Cache Connection Manager.
11. In the "Map Input Columns..." window, connect "Order Date" from the Input column to the "OrderDate" from the Cache Column by dragging one to the other. Do the same for the remaining field. Click "OK".
12. Drag your Matched and Non-Matched outputs to your desired destination. For this example we used TF Terminator Destinations for both.