SSIS - How To Use Flat File Or Excel File In Lookup Transformation [Cache Transformation]


We use Lookup Transformation to join input data with reference data to get required data from Reference Data. By using Lookup Transformation we can connect to databases and get the reference data. But our company has An Excel file that they want to use as reference. The business users can add or remove the records from this Excel. Before we load any data to our destination table we need to verify against this Excel sheet if data matches on required columns.


Once solution to this problem can be, Create the Staging Table and then truncate on each load and load from Flat file and then use in lookup transformation for reference.

In SSIS 2008, Cache Transformation was introduction so we don't have to load the data into Staging/Temp tables to use in Lookup Transformation. We can load the data in memory by using Cache Transformation and then use in Lookup Transformation.

Let's say if I have a source table as shown below
Fig 1: Source Data for Cache Transformation Example

As we can see that I don't have the CountryName. I need to get the CountryName from Flat file by joining the input and reference data on Country Code.

Fig 2: Flat File Reference Data for Cache Transformation

Step 1: 

Create an SSIS Package. Inside SSIS Package bring the Data Flow Task. In this Data Flow Task we will load the Data from Flat File to Cache Transformation. Bring the Flat source and create connection manager for flat file and then bring the Cache Transformation and configure as shown below.
Fig 3: Create Configure Cache Transformation in SSIS Package

Fig 4: Configure Index Position for Mapping Columns in Cache Transformation.

As we are going to use CountryCode for joining with out input column. Set the Index position for this column to 1. If you are using more than one column for joining , you can change the Index position. For first column, It will be 1 and then for second 2 and so on. We don't need to Index the Column/s which we are not using in Join.

Fig 5: Load Reference Data To Cache by using Cache Transformation in First Data Flow Task

In Fig 5, we have loaded the reference data from Flat file source to Cache by using Cache Transformation in Data Flow Task.

Step 2: 

Bring the second Data Flow Task and connect with First Data Flow Task. Inside 2nd Data Flow Task, Read the data from SQL table by using OLE DB Source.Bring the Lookup Transformation and attach OLE DB source to it and configure as shown below.

Fig 6: Configure OLE DB Source

Double Click the Lookup Transformation and then in Connection Type choose Cache Connection Manager as shown below.

I Left Fail Component in How to handle rows with no matching entries. You can configure as per your requirement. You can redirect or fail if there are some record/s that does not match with reference dataset.
Fig 7: Use Cache Connection Manager in Lookup Transformation

Fig 8: Choose Cache Connection Manager in Lookup Transformation

Go to Columns and then connect the columns on which do you want to join. I have connected CountryCode column from Source to CountryCode in Reference Dataset. By using the Check Boxes, Select the columns those you want to retrieve from reference dataset. In our case we want to retrieve Country Name and finally hit Ok. 
Fig 9: Map the Columns from Source to Reference Data Set

Add the destination and then map the columns. Just to show the output, I have used Multicast Transformation as Test Destination and added data viewer to show the records. 

Fig 10: Output from Lookup Transformation

Our final package will look like below fig.
Fig 1: How to Use Cache Transformation in SSIS Package


  1. This comment has been removed by the author.

  2. Thank you so much for providing such a explanatory post.I loved each and every aspect of it since I always needed to use excel file during SSIS operations but I could never do it since it was a tricky affair for me anyways this post is still laudable.

  3. That is really nice to hear. thank you for the update and good luck. free download

  4. hmmm, mobile marketing is quite interesting, maybe there would be some more money making oppurtunities in it“ help for Excel

  5. very nice article understand your point but you miss about car safety products check this out

  6. LaptopLelo on low-cost best Backpacks Pakistan . At Laptoplelo, we offer messenger bags, sleeves, classic bags, premium bags, backpacks, travel bags, rollers, and MacBook bags of reputable brands such as Targus, genius, Logitech, Tucano, and verbatim.

  7. Great post. Excellent sites! If you are looking for customised stickers india.
    Get connected with us for more details.

  8. The Microsoft Visual C++ runtime library error is annoying due to the fact that your application closes every time it appears. Most users ignore this kind of error message after it occurred. Microsoft Visual C++ 14.0 is required

  9. This comment has been removed by the author.

  10. In an era where laptops are integral to both work and leisure, safeguarding these devices becomes paramount. The right laptop bag not only complements your style but also offers robust protection against potential damage. Laptoplelo stands out as a hub for premium-quality laptop bags that merge style with unparalleled device security.

    Unveiling Laptoplelo A Haven for Laptop Protection

    Laptoplelo, a cutting-edge online platform, specialises in curating an extensive collection of laptop bags designed to cater to diverse needs. Whether it's sleek professionalism, outdoor durability, or trendy aesthetics, the platform hosts a wide array of options to suit every preference.

    Laptoplelo's Quality Standards

    At Laptoplelo, the emphasis is on top-tier protection for your laptop. Each bag undergoes rigorous quality checks and is crafted from materials that guarantee durability and resilience against daily wear and tear. From shock-absorbing padding to water-resistant exteriors, these laptop bags are engineered to shield your device from various hazards.

    Range Diversity Catering to Every Need and Style

    fashion. Whether you're a professional seeking a sleek, leather laptop briefcase or an adventurer in need of a rugged, weatherproof backpack, the platform houses an extensive range to match your lifestyle and preferences.

    User-Friendly Interface

    One of the standout features of Laptoplelo is its user-centric design. The website's intuitive interface ensures a seamless browsing experience, allowing users to filter through options based on size, style, and even specific laptop models. Detailed product descriptions and reviews empower customers to make informed decisions.

    Beyond Protection Laptoplelo's Value Proposition

    Laptoplelo isn’t just about selling bags; it's about fostering a sense of trust and satisfaction. The platform extends beyond product sales, offering valuable insights into laptop care, maintenance tips, and occasional promotions to its dedicated customer base. This commitment to customer satisfaction sets Laptoplelo apart in the market.

    Testimonials: Real Voices, Real Experiences

    Customer testimonials serve as a testament to Laptoplelo's commitment to quality and customer satisfaction. From professionals praising the bags’ functionality to students lauding their style and durability, these firsthand accounts validate the platform's dedication to providing top-tier laptop protection.

    Conclusion Elevate Your Laptop's Safety with Laptoplelo

    When it comes to safeguarding your device, settling for anything less than optimal protection is not an option. Laptoplelo stands as a beacon of reliability, offering a diverse range of top-quality laptop bags that not only shield your device but also elevate your style quotient. Visit Laptoplelo today and redefine how you protect and carry your most valuable tech companion.