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

I have excel file in that I have a fixed number of sheets (3 sheets example ) with same Metadata. This file I am getting daily we need to load this data into the database.

See the example file

  

In this excel file, I have 3 sheets named as 1000 records, 100 Records, 5000 Records having 1000,100,5000 records data.

I need to load this data.

These are multiple ways to load this data. Here I will see the simple demo how we load these data in a simple way.

Let’s see the demo.

I am taking the data flow task

                               

In data flow, I am taking source as excel

  

Now I am creating an excel connection manager and configuring the source file.

   

Now double click on the Excel source component.

In the connection manager tab, we need to select the connection manager and select SQL command as Data access Mobe

In SQL command text, write the sql command to pull the records form all sheets.

  

Keep in Mind: It will pull the records from the provided sheets only. For example, I wrote sql script to pull the records from 3 sheets only if in the excel file have one more sheet, in this case, it will put 3 sheets (as above written in the SQL command) only.

Click ok.

Now taking destination as OLEDB Destination

  

And doing the configuration.

Now the package is ready to run.

See the records in the table.

  

Now running this package.

I am expecting 6100 records load into the database

  

Now see in the database

  

Got the Expected result.