You could use an expression in the connection manager to change the name of the sheet. Logic in your SSIS package (perhaps a Foreach loop) would deal changing the value of the expression each time through the loop. This assumes that you know the names of the sheets in advance or can write code to get a list of sheets from each spreadsheet you process.

If you know them in advance, write a SELECT statement that returns the list of sheets. Run the SELECT in a exec sql task and send the result to a variable. Use the variable in the loop (from memory, loop through an ADO Recordset will be what you want).

As for the other option, I haven't ever needed to find the name of all sheets in a spreadsheet but I am pretty sure that there is a way to do it.