I have an SSIS package with a Foreach Loop Container that reads Excel files in a specified directory. I get an error opening the file: Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Envision" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

The variable that I assign the Excel file path to does get the correct value, however the package can't open the file.

I found articles describing that I need to supply the actual Excel file Connection String in the variable, not just the file path. I had some issues doing that so instead I assigned the Excel Connection String inside a script task. That solved my issue.


Dim strFile As String
strFile = Dts.Variables("User::XLSX_File").Value.ToString
Dts.Connections("Excel Connection Manager").ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFile + ";Extended Properties=""Excel 12.0;HDR=YES"";"
Dts.Variables("User::ExcelConnString").Value = Dts.Connections("Excel Connection Manager").ConnectionString.ToString

Only subsequently I found that the Excel Connection Manager has a property called ExcelFilePath. So it is possible I didn't have to add the Script Task :)