Loop Through Excel File in SSIS

You can loop through excel files using SSIS. This will use the For Each Loop container and a Data Flow task.
 

First create a variable named strExcelfile as a string variable; you can leave the value blank.
 

Next, drag in a For Each Loop. Set the enumerator to For Each File, and point it to the folder where the excel files exist and type .xls or .xlsx for the file type.  In this example the excel files are in c:\test\excelfiles\.

Then click on the Variables Mappings node and add the strExcelFile variable with an index of 0.
 

Next, drag in a Data Flow task and drop it in the loop container. Open the Data flow and drag in an Excel Source. Set the Source to one of the excel files in the folder above. This will set the column names and the metadata for the files. Each file in the loop must have the same metadata. If they have different column widths or data types, then you cannot use this technique. You will need seperate dataflows.

Now you will map the excel file to the connection. Click on the Excel connection in the connection manager. This was created when you created the Excel source in the Data Flow. Click on Expressions in the properties windows and open the expressions editor for the Excel connection manager. Select the ExcelFilePath property and drag in the strExcelFile variable.
 

One Last step is to set the Data Flow to delay validation. This is so the data flow will not check for the excel file until after the file name is loaded into the variable.
 

Note: The excel file will have a red "X" on it. This can be ignored and the package should run. If you need to reopne the excel source then you will need to put a valid file name into the strExcelFile variable value.

Let me know if you have any questions.