OK, I surrender. I've read through about 100 posts from various other poor slobs trying to get SSIS to swallow a variable file name for an Excel connection manager. Tried everything recommended to the best of my (limited) understanding and still get the dreaded connection error message.
Here's the laundry list of stuff I've done:
1) Changed package debug option Run64BitRuntime to False
Then the package would run if I manually set the Excel Connection Manager to a specific file.
Now I just want to be able to call the bloody thing from the cmd line and set the variable strFile to the file I want it to process.
2) Added the global variable strFile and set the value to a known location of a file that could be successfully read when set manually.
3) Set the Excel Connection Manager's ConnectionString property = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::strFile] + ";Extended Properties="EXCEL 8.0;HDR=YES";
4) Set connection DelayValidation to False
5) Set Control Flow Execution property to DelayValidation = True
Tried running again. Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER . The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009
Setting the Connection Manager Connection String sets the ExcelFilePath and ServerName properties to " + @[User::strFile] + " which seemed odd so I did cycle through an option of setting those to just @[User::strFile] but that fails as well with the same error msg.
6) Tried setting the ConnectionString property through the property expressions. Same error.
7) Fiddled with any combination I could think of with these settings.
Any suggestions for other things I can try? Sacrificing a small animal to some obscure deity? Reconfigure my project to use a static file name? Find a server with SQL Server 2000 and write this in DTS?