How to read data from multiple Excel files with SQL Server Integration Services(New Senario)
When you initially pull the file paths from the ExcelFilePath table, trying returning just names of the folders holding the Excel files that will be used and make sure these end with a \ (i.e. C:\Excel_Exercise\Excel_Source\).  You may need to used a T-SQL function like RIGHT/SUBSTRING, but doing this will allow you to load the files using the method outlined below.  Also, are you filtering for only "Active" file paths in the SQL query from the Execute SQL Task?  As you're probably already doing, make sure to set the DelayValidation and ValidateExternalMetadata properties accordingly.


The First Foreach Loop will be a Foreach ADO Enumerator using the object variable from the Execute SQL Task as the source variable.  In the Variable Mappings pane, add a string variable that will hold the file directory at index 0.
Inside the first Foreach Loop, add another Foreach Loop as a Foreach File Enumerator.  On the Collection pane, add an expression for the Directory property that uses the string variable from the outer Foreach loop with the directory.  On this Foreach Loop, add a variable in the Variable Mappings pane the will be used to hold the full file path (including file name/extension).
Within this Foreach Loop add a Data Flow Task that loads from an Excel Source to the desired destination.  On the Connection Manager for the Excel Source, create an expression for the ExcelFilePath and add the variable holding the complete file path and name/extension.