How to get SSIS to import an Excel file with non-default Worksheet name?

I have an SSIS package that loops through a folder and imports all the excel files into an OLEDB destination. The package works fine as long as all the worksheet names are set to default. It br开发者_运维技巧eaks if the worksheet name is different. There are a couple of examples online on how to import multiple worksheets. How do I get SSIS to import an Excel file with non-default Worksheet name?

Check out Programming Foreach Loop Container – Enumerating Excel Sheets. It uses a Foreach Loop container to iterate over whatever sheets are in the workbook.

You can set OpenRowSet property of Excel Source. Something like "MyNotDefaultWorksheet". You can also set this property from package variable and then set this variable programmatically.