There is a reasonably useful article on TechNet that describes most of the work needed to import data from multiple worksheets in an Excel file with SSIS.

http://technet.microsoft.com/en-us/library/ms345182.aspx

However, the last step in this article leaves you with a number of assumptions. Once you have followed all the steps listed in the technet article, this is what you have to do.

1. Add a Data Flow task to the For Each loop container.

2. In the Data Flow container, add an Excel Source component.

3. Set the OLE DB connection manager to the Excel connection manager that you would've already setup.

4. Change Data access mode for the Excel Source component to 'Table name or view name variable'. This will allow you to use the variable name to specify the name of worksheet, instead of hard coding it.

Note: You may want to use a specific sheet within the Excel file first in order for SSIS to pickup the various column names. Once you are done with the column mapping, you can change this setting back to variable name.

Now you can continue on with whatever you want to do with the data that is read from each worksheet, one worksheet at a time.

Hope this helps.

-- Adnan