In your DTS package you can have a short ActiveX Script task that extracts a list of sheet names and set the connection properties.
Open the Excel workbook using an ADO connection. Then from memory, I think its the OpenSchema method that returns a resultset that list the sheets in the woorkbook.
Take a look at the examples over at http://www.sqldts.com. Specifically those that deal with dynamically setting connection properties and looping.
--------------------
Colt 45 - the original point and click interface