SSIS and 64-bit Microsoft Access Database Engine

Question

Our dev server has 64-bit SQL Server 2008 R2 with SSIS. To be able to import XLSX files directly from T-SQL (via OPENROWSET ), we installed the Microsoft Access Database Engine Redistributable 64-bit on the dev server. A 32-bit version is already installed on the server, so we installed it using the / passive switch.

Since this happens, our existing packages fail. One of them reports

This step did not generate any output. The return value was unknown. The process exit code was -1066598274. Failed to complete the step.

It’s just interesting if anyone else managed to set up their server to use OPENROWSET files for XLSX and also to have SSIS on the same server, or does anyone know if this configuration is possible?

Edit: I have to add that unsuccessful packages start from an SQL agent job. When I connect to the Integration Services Manager server through SQL Server Management Studio and run the package (by navigating to the package, right-clicking on it and selecting "Run Package"), it succeeds.

Answer 1

Personally, I gave up trying to support this scenario - I think the “passive” installation is a hack that actually doesn't work and usually breaks your other ADE installation. I would completely disable ADE and then only reinstall 32-bit. Then I force all packages to run through SQL Agent to run as 32-bit (as described by Brian).

After this process, you get some hope for debugging problems, since you can open your package in Visual Studio and it will use the same 32-bit driver. You will also use a consistent driver when executing from SSMS (also 32-bit).

Yes, performance degradation may occur, but SSIS performance is usually limited to some extent by I / O or network speed, and not just by pure engine bandwidth.

Answer 2

Yes it is possible. You must specify to use 32-bit runtime in the job step. Open each step of the job that launches one of your SSIS packages, and click "Run Options." Check the box at the bottom of "Use 32-bit runtime."

Answer 3

I had a "Step did not generate any output. The return value was unknown. The process exit code was -1066598274. I found a walk for myself: I switched from xlsx to xls , and the error no longer raised.