How to Use Excel Data in Lookup Transformation in SSIS Package - SQL Server Integration (SSIS) Tutorial


We get our reference data or lookup data an Excel File. Business users can make any changes to the data. They can add new records or delete or update them in Excel. Every time we need to load the data from Source Flat File, we want to perform a lookup against Excel file and load only the records which have the matching records in Excel File.
How can be use Excel File in Lookup Transformation?

What we will learn in this video
How to read data from an Excel File and Load into Memory by using Cache Transformation
How to read data from Flat File Source in SSIS Package
How to use Derived Column or Data Conversion Transformation to perform Data Conversion if Required in SSIS Package
How to use Cache Transformation Connection Manager in Lookup Transformation in SSIS Package
How to get only Matching Records from Lookup output in SSIS Package


  1. It is interesting, pleasant to see this blog much obliged for sharing this. Visit Jiva IT Solution for
    web development service and more.

  2. I think you will not need a lookup transformation if you use SSIS Upsert. Although this is a third-party product but I still think it will be useful.