I think you'd have to do this in a script task and then pass the value into a variable to use later on in a data flow task excel source.

You can get the name of the first sheet in a workbook each time, have a read of this and it should help you.

Best of luck 🙂