I don't know what error you are getting. I just tried your scenario by putting 2 excel files in the source folder with data in Sheet1 and Sheet2 respectively. It is working perfectly fine for me.
May be SSIS engine searches for the right sheet to use. I am using SSIS 2008 R2.