How to read data from multiple Excel files with SQL Server Integration Services(New Senario) Hope you're doing well.
I have a scenario here.
As you can see I have a table with two main columns :


What I need is to read each "FilePath" which is "Active" right now and then read the Excel files in that path. So I assume that I need at least 2 
"For Each loop Container" inside each other. I've already done some part of the scenario :


Within the "Execute sql task" I'm retrieving "FilePath" which are active from the table and putting them in an "object" variable (My first variable in SSIS) . 

Then I have my second variable in SSIS in which I keep the name of each "FilePath" each time the outer loop is being executed.

At the end I have my third variable which will change each time the inner loop in being executed and it carries the Excel files.

The outer loop must execute to the number of active rows in meta data table and the inner loop must execute to the number of file within the path.

The point is I receive no error after executing the package, but the data won't be inserted in my "OLEDB Destination"
I was wondering if you could help me with this issue.
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.