For anyone still working on this, I found out how to pass the table name gathered in the ADO NET enumerator to the Excel source in the data flow.

1. Inside the ADO NET enumerator, add a data flow task.

2. Inside the data flow task, add an Excel source.

3. Open the Excel source, and point to the Excel data source set up in the file enumerator.

4. Select one of the sheets that shows in the drop down.

5. Close the Excel source.

6. Select the Excel source.

7. In the properties window, copy the name of the sheet you selected. It's in the 'OpenRowset' property.

8. Set this as the intitial string in your sheet name variable created to gather the sheet name from the ADO NET enumerator.

9. Select the Excel source.

10. In the properties window, change the 'AccessMode' to OpenRowset From Variable. This will change the property 'OpenRowset' to 'OpenRowsetVariable'.

11. Change the 'OpenRowsetVariable' to the variable created to hold the sheet name via the dropdown.

12. Open the Excel source. You should see your variable as the sheet name.

13. Click the 'Columns' tab to verify you can connect and mess with columns.