Additionally here are a few sites that have helped me out trying to accomplish something similiar: 
https://danajaatcse.wordpress.com/2010/05/05/extracting-data-from-multiple-sheets-in-an-excel-file-in-ssis/
https://www.mssqltips.com/sqlservertip/4157/how-to-read-data-from-multiple-excel-worksheets-with-sql-server-integration-services/

Hope this helps!