Using SSIS we had a Excel Source to import regularly into a database. We constructed a simple project in SSDT and it ran successfully. We then deployed the project, started an SQL Agent job step with a step as an SQL Server Integration Services Package. “CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER” and “Excel Source failed validation and returned error code 0xC020801C.”.
Since the servers are 64 bit and the excel drivers are only 32 bit we changed the debugging configuration properties of the project debug options (Project -> from Run64BitRuntime from True to False as below.
Deploying the revised project and now expecting success, we got the same error – all the information pointed to it running in a 64 bit environment.
The solution is to go into the job step and then go to the configuration tab, then the advanced tab and tick the 32-bit runtime box as below.
The causes the SQL Agent job step to run in the context of 32 bit and the job succeeds.