One way I've done this which may help you is to create a control table in SQL Server.
Something like this:
CREATE TABLE [dbo].[ExcelSource](
[WorkbookName] [varchar](255) NULL,
[FilePath] [varchar](255) NULL,
[SheetName] [varchar](255) NULL,
[CellRangeToQuery] [varchar](25) null,
[Active] [varchar](5) NULL
) ON [PRIMARY]
In SSIS, I created a SQL Task that queries the control table to get the list of all worksheets in a workbook, the cell range for particular data and target information. Loop through this data values and dynamically set SSIS variables to connect to the worksheet, then query that worksheet with its corresponding cell range to import. Then after the transformation use the control tables target value to set where that worksheet data is going.
So essentially you loop through the above data and build a sql statement that you store in an SSIS variable. So in your data flow task in your excel data source task, your data access type is SQL Command from variable.
So in above table it you had a record like
your query will be built like the following: when you connect to Workbook1, you will pull all the data from the cells in the CellRangeToQuery field.
Select * from [Sheet1$A1:Z25]
You can also get fancier and maintain your list of workbooks/worksheets in excel and have SSIS import that list into the ExcelSource table and then loop through it.
Here are a couple of other articles that outline similar process:http://www.sqlservercentral.com/Forums/Topic637794-148-1.aspx
3 part series on using excel files in SSIShttp://dougbert.com/blog/post/excel-in-integration-services-part-1-of-3-connections-and-components.aspx