The lookup transform in SSIS 2008 gives you the ability to join data and eliminate non matching rows. One of the limitations of the lookup is the fact it requires an OLEDB connection. However, with the cache connection manager you can use just about any other data source as your lookup table. If you have an excel file that contains your lookup information, it would be nice to be able to lookup the information in the excel file without having to load the excel file into a table.
To accomplish this we will load the excel file into a cache connection manager and use this in the data flow. Below is my SQL Table with first names and my excel file with last names. They both have matching ID columns. I will match up the id in the lookup transform to combine the names.
Excel File with First Names
The Excel source is just a simple source with the ID column and the First Name Column. The only change you may have to make is to the data types in the excel source. I had to change the data type of the ID field to integer. That is because my table has an ID column with a data type of integer. To change the data type, right click on the excel source and select Advanced Editor. Click on the Input and Output Properties Tab, expand the output columns folder, select the ID column, and change the data type property as needed.
After you have the source ready, drag the green data flow line to the cache transform. Click the New button and give the cache connection manager a name. Then click on the columns tab and set the ID column’s index property to 1. You must have at least columns with an index property value greater than zero.
In the next data flow we will have and OLEDB source from the SQL table and a lookup transform using the cache connection. I also have a terminator destination from task factory, just so we have a place to put a data viewer to see if the first and last names are joined.
You can use this same method to load just about anything into cache and use it in the lookup transform.