1. You need 1 Data Flow Task, containing an EXCEL source Connection and a SQL destination connection

2. Script Task to get list of sheets within the excel file

3. For Each ADO Loop Enumerator to loop through sheets

You also need to dynamically set source connection using variables/expressions. Currently, I don't have a working example/sample of it. May be you can look BOL for information on above.