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...