Issues Looping through Excel Files in SSIS 2012 (Foreach ETL)

I am trying to process Excel files (2007 .XLSX) in a directory on the local machine. There have been a number of issues that have come up of which I will list. Before that let me explain what I want to do and how I have set things up and all my attempts so far.

Set Up

• Microsoft Visual Studio 2012
• Microsoft Windows 7 (32 bit)

Goal

Create a simple ETL to loop through Excel files in a folder and load data into a SQL server table.

Current Attempts

Note: I am very much familiar with programming and especially loops, however in SSIS I am struggling to get the Excel file from even being read.

I have tried and am doing the following:

• Created a package
• Created a FOREACH loop container task

FOREACH Loop Task

• Set as Foreach File Enumerator
• Expression assigns the Directory to the variable which has the value of the directory ('C:\Users\Me\Desktop\Excels\')
• Under Variable Mappings I have specified a variable at index 0
• Set to using File and Extension
• For the moment I am looking at all files (.) Note: I have used watch on debug to determine if the variables get populated. Now I have tried an expression task to build the full path before moving onto the data flow as well as just using the two variables assigned.

Data Flow (after FOREACH loop container)

• Set up Excel File Source
• Set up OLE DB Destination

Excel Source (inside Data Flow as the first task)

• Set up the columns initially using one of the files (preview and input/output's are shown) Note: Data is shown so the connection seems fine

Excel Source Connection Manager

• DELAY VALIDATION = TRUE
• EXPRESSIONS = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::excelFullPath] + "Extended Properties='EXCEL 12.0 XML;HDR=YES';"
• ExcelFilePath = @[User::excelFullPath]
• FirstRowHasColumnName = TRUE

Note: I have tried a combination of using the connection string and the excel file path property. I have tried those on their own.

Note: I have two Excel files in the folder for the moment. They are identical with different file names and YES the columns are the same.

Errors Received

The latest error upon RUN is:

SSIS package "C:\Users\biadmin\Desktop\0006AssocImport\0006AssocImport\Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at Data Flow Task, Excel Source 1 [2]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E37.
Error: 0xC02020E8 at Data Flow Task, Excel Source 1 [2]: Opening a rowset for "MAIN$" failed. Check that the object exists in the database.
Error: 0xC004706B at Data Flow Task, SSIS.Pipeline: "Excel Source 1" failed validation and returned validation status "VS_ISBROKEN".
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "C:\Users\biadmin\Desktop\0006AssocImport\0006AssocImport\Package.dtsx" finished: Failure.
The program '[6272] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).

Another Error Received Earlier

SSIS package "C:\Users\biadmin\Desktop\0006AssocImport\0006AssocImport\Package.dtsx" starting.
Information: 0x4004300A at Import RAW Excel Data, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at Package, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft Access Database Engine"  Hresult: 0x80004005  Description: "Could not find installable ISAM.".
Error: 0xC020801C at Import RAW Excel Data, Excel Source [2]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Import RAW Excel Data, SSIS.Pipeline: Excel Source failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Import RAW Excel Data, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Import RAW Excel Data: There were errors during task validation.
Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "C:\Users\biadmin\Desktop\0006AssocImport\0006AssocImport\Package.dtsx" finished: Failure.

These are not all the errors received (I did not record all of them) but at the moment its just failing on one particular error at a time.

Note

I have seen quite a few YouTube tutorials and read a few outlining the exact steps. I have seen one where they set the connection manager from a Script task before getting to the Data Flow task. I saw one where the ConnectionString property was set only to the variable that holds the Excel File Path.

Questions

1. What am I missing in my set up
2. After configuring the connection manager to look at a variable for the file is there anyway of still configuring inputs and outputs on the Excel Source task because at the moment its showing an error
3. What is the developer preference in SSIS for this sort of situation where I prefer to see the column mappings on the configuration box (Script or Flow)