See attachment

You have several ways of doing that, but one would be to configure your loop to use Expressions for FileNameRetrieval and Directory.

Base those expressions on variables and set the variables'...

Issue is not getting the good number of rows.

Issue is I'm not getting the first column (the one that has dates in it).

However I found a workaround : I added...

If your question is about "How to have a dynamic excel source", a quick google search returns this:

setting up an ssis package...

To achieve this you should set the "ExcelFilePath" and "ExcelServerName" properties of your excel connection as expressions.

You'll have to create a SQL Job to do that.

In SQL Server Management Studio connect to your SQL Server engine, go to "SQL Server Agent" (You many need to enable...