SSIS Tutorial Part 65-Use Excel Data in Lookup Transformation in SSIS Package (Cache Transformation)

SSIS Tutorial Scenario:
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

Link to the blog post for this video with script if used

To follow Step by Step SQL Server Integration Services(SSIS) Video Tutorial By TechBrothers, please open below link.

[farooquekhan]

Hi, I have two csv file, and I want to use lookup, can I use csv file as a reference dataset in for lookup data

[kashifkhanspecial]

Hi,
I hope you are doing good and staying safe and healthy.

I have 3 Excel sheets, in Sheet1 have 3 columns Name, Salary and Area, and in Sheet2 have 2 columns Name and Salry and in Sheet3 have 2 column Name and Area, and in the Sheet1 I want to fetch Salary from Sheet2 and Area from Sheet3 against the common column Name, and if the Name in the Sheet2 and Sheet3 is showing multiple time then I want to fetch only 1st instance value (Salary from Sheet2 and Area from Sheet3).

Please help me how can I perform this type of lookup in SSIS.

Thanks in advance for your help.

Thanks
Kashif Ahmad Khan

[sugumarganesan]

Can you share Expression of different conditional split Transf