I'm trying to parse through multiple excel files using Foreach Loop Container and getting the error blow. Whats annoying me is the for each loop works when the "Excel File Path" is set to 1 file in excel connection manager. But it loops through the same file multiple times. However, I need to loop through each file once and move to the next file. so I need to add an expression to excel connection manager. And its when I add an expression of the variable is when the error occurs
TITLE: Package Validation Error
Package Validation Error
ADDITIONAL INFORMATION:
Error at Data Flow Task [Excel Source [2]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager 1" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error at Data Flow Task [SSIS.Pipeline]: Excel Source failed validation and returned error code 0xC020801C.
Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
Error at Package [Connection manager "Excel Connection Manager 1"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft Office Access Database Engine" Hresult: 0x80004005 Description: "Invalid argument.".
(Microsoft.DataTransformationServices.VsIntegration)
BUTTONS: