I have a basic question which I can't address on my own. The basic task is to loop through Excel workbooks and dump the results into a table in a database.
I am using SSIS and am basically following the guide located here: https://www.codeproject.com/Tips/803195/Foreach-File-Enumerator-in-SSIS?msg=5361786#xx5361786xx
The problem I'm encountering is that in the last step when I have to input a variable in the expression of the data connection, it breaks? I've tried setting the expression as both a connection & an Excel file path.
For reference, the relevant parts of the error message are:
Error: 0xC0024108 at Package, Connection manager "Excel Connection Manager 1": The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager. Error: 0xC0017004 at Package: The result of the expression "@[User::FilePath]" on property "\Package.Connections[Excel Connection Manager 1].Properties[ConnectionString]" cannot be written to the property. The expression was evaluated, but cannot be set on the property. Error: 0xC0202009 at Package, Connection manager "Excel Connection Manager 1": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. Error: 0xC020801C 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.
User contributions licensed under CC BY-SA 3.0