I am trying to create a package that loops through several excel files to import data into a database. The problem I am experiencing is that as soon as I assign an expression variable for connection string in the Excel connection manager, I get the following error: Error 1 Validation error. [xyz]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "[XYZ]" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
I'm using ssis 2012. I have set DelayValidation to true on both data flow task and excel connection manager.
I have applied my variable to both ConnectionString and ExcelFilePath but this error persists: