We often come across a situation where we have to use flat files as a reference to load and manipulate data. In SQL Server Integration Services (SSIS), there is a transformation called Lookup, which performs lookups by joining data in input columns with columns in a reference dataset.
However, SSIS provides OLE-DB Connection to connect with the source, and you have to create a temporary table in OLE DB to use Flat File as Lookup. So, this article explains how to perform the Lookup transformation on flat files using cache transformation.
This SSIS task is divided into two dataflow tasks:
- Cache Data Preparation
- Utilizing Cache Data
The following steps are used to perform a Lookup on flat files.
1. Cache Data Preparation
In this section, we have to create a data flow task that includes an Excel file source and cache transformation to store the data in memory so it can be called in later steps. In short, the cache operation is performed at this stage. Follow the steps below to store data in the cache.
- Create a data flow task.
- Create a flat file (Excel file in this tutorial) as a source in the data flow task.
- Drag and drop Cache Transform and configure its properties accordingly.
- Create a new Cache Connection in Cache Transform properties.
- Edit the connection and create a CAW file extension to store cache data in this file.
- Map all the required columns and set the Index Position to 1 on the column you want to cache data in.
- Execute the task.
2. Utilizing Cache Data
After executing the above data flow task, we use Excel as another source and perform a Lookup on the cached data.
- Create a data flow task.
- Create an Excel file as a second source in the data flow task.
- Drag and drop the Lookup component and configure its Connection type as Cache connection manager to use cached data as a Lookup source. In this scenario, we redirect unmatched rows from the Excel file into OLE DB.
- Add cache file (CAW) in properties and map the required columns.
- Now add the OLE DB component as the destination to store the unmatched rows.
- Execute the task.
Conclusion
After executing both tasks, you can have unique data in the destination database after looking it up in an Excel file.
OLE DB Data before:
Customer Lookup Data:
OLE DB data After:
Cache Transform transformation creates a reference dataset for the Lookup that we can utilize in the Lookup transformation without accessing the database again and again. Moreover, it also allows us to reduce the database load.
We hope this article helps you perform Lookup transformation on flat files using cache transformation. If you need any assistance with Lookup transformation, contact us at [email protected].