Create connection to our Excel source file as shown below
Choose Microsoft Office 12.0 Access Database Engine OLE DB Provided as shown below
Press Ok as shown above in number 3. Provide the File Path as shown below
Click on All and Provide values as shown below
We are all done with configuring our Excel File Connection.
Bring For each loop container to the Control Flow Pane and configure as shown below
Enumerator : Foreach ADO.NET Schema Rowset Enumerator
Connection String : The OLE DB Connection String that we have created in Step 1
Schema : Tables As we are interested to get the names of sheets
Go to Variable Mapping and map the variable to Table_Name as shown below
Set Index from 0 to 2 as Table_Name is on third number in List
Bring Data Flow Task to Control Flow pane inside Foreach Loop Container. Open Data Flow by double Clicking and then bring ADO.NET Source as shown below and configure. One thing to notice here, we do not have option to provide variable in which we have saved our SheetName , For now Choose any sheet you like as all Sheets in Excel are with same metadata.
Drag Derived Column Transformation to Data Flow pane and Connect ADO NET Source to it. Perform data conversation according to destination columns and also generate new column DER_SheetName by using SheetName variable.
After that bring OLE DB Destination and connect Derived Column Transformation to it and map the columns.
Till here our package is ready but only Table Name(SheetName) is hard coded in our ADO NET Source. We want to change it to SheetName Variable so with each iteration it should change so we can load all the Excel sheets.
To do that , Click on Data Flow Task and go to properties and then Expression and configure as shown.
Set the data flow property Delay Validation=True so it should not validation as sheet names will be provided while execution not at start of package validation.
Final Output :
Execute package and check the destination table if all the records from all excel sheets are loaded successfully.