Home » Your Daily TechBytes » SSIS – Flat File as a connection in Lookup Transformation

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: 

  1. Cache Data Preparation 
  2. Utilizing Cache Data

Cache TransformationThe 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. 

Cache Data Preparation


  • Edit the connection and create a CAW file extension to store cache data in this file. 

CAW file


Cache Connection Manager


  • Map all the required columns and set the Index Position to 1 on the column you want to cache data in. 
  • Execute the task. 

Task Execution

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. 

Transformation Editior

  • Add cache file (CAW) in properties and map the required columns. 

Caw file editor

  • Now add the OLE DB component as the destination to store the unmatched rows. 
  • Execute the task. 

Destination of Task


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: 

OLE Database

Customer Lookup Data: 

Customer Data


OLE DB data After: 

OLE Database 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 info@xavor.com. 


Umair Falak

Umair Falak, is a Search Engine Optimization (SEO) Executive at Xavor Corporation. He loves to play with websites ranking and analytics.