Load multiple excel sheets (same Meta data) in SSIS

This is the common scenario where the client sends the data in the excel sheet with multiple worksheets. Our requirement is to read all worksheet and load the data into the database.

There are multiple approaches to read the excel sheet. Here we will describe a simple way to read the excel and load the data into the database.

If we have fixed number of worksheet the try below solution

Load data from multiple Excel sheets (fixed number of sheet with same Meta data)into the database

https://bageshkumarbagi-msbi.blogspot.com/2019/01/load-data-from-multiple-excel-sheets.html

If we don’t have fixed worksheet then see in this demo

For example, I have an excel sheet. In that, I have 5 sheets

   

We need to read this file and load it into the database.

Let’s see the demo

I am taking Foreach loop container

   

Before selecting a collection we need to create a variable.

       

Double click on this container.

 

 

Select Foreach ADO.NET Schema Rowset Enumerator.

Now create a connection.

   

  

Click on the new connection.

   

Select Provider as Microsoft Jet 4.0 OLE DB Provider and click ok.

Now browse the file

   

                           
    

 Click on the all as shown below.

                        

Update Extended Properties to "Excel 8.0".

Click on Text connection

    

The connection has done successfully.

  

Click ok.

Now go to the Variables mapping and map the variable

    

Taking data flow task inside the for each loop container.

     

In the data flow taking source as Excel

Creating excel connection manager and configuring it.

   

 

First, we map with any sheet

   

Taking derived column transformation. In this I am creating a column Sheet name

       

   

Now I am taking OLEDB destination.

  

Doing the mapping

Now time come to make your excel source as dynamic.

Click on the excel source

Select data Access mode as a table name or view name variable

     

And select the Variable name as Sheet name.

Now we need to set the delay validation property true.

Read delay validation: https://bageshkumarbagi-msbi.blogspot.com/2016/09/delay-validation-properties-in-ssis.html

  

Now the package is ready to run.

  

Before running this package see the data in the database.

  

Now running this package.

 

Package completed successfully.

See the records in the database.

  

See the sheet loaded in the database.

  

Now I am adding one worksheet in the excel named ‘TestSheet’ and loading it.

   

Now loading this file to the database.

 

Hope it will help you.