I have used SQL Servers Import Data wizard to import data from an Excel spreadsheet.
The Import Data Wizard worked fine to import the data.
It also worked fine to save the SSIS package.
I can then log in to Integration Services on the server and run the package from within Integration Services.
However, when I try to run the package from SQL Server Agent, I get the error message:
The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.
I've seen suggestions to set the "Use 32 bit runtime" option, but this had no effect. I've seen suggestions to set something similar in BIDS, but I didn't use BIDS to generate this package. I've seen options to use the 32-bit version of DTExec but I don't think the 32-bit version is installed on the server.
SQL Agent Job definition:
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'import data', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'SSIS', @command=N'/SQL "\"\ExcelImportPackage\"" /SERVER MyDBServer /X86 /CHECKPOINTING OFF /REPORTING E', @database_name=N'master', @flags=0
Given that the package works both from the Wizard and from Integration Services I believe the server has everything needed to run the package from SQL Server Agent, but I cannot see how to do it.