SSIS and 64-bit Microsoft Access Database Engine

Question

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

Since doing that, our existing packages are failing. One of them reports

The step did not generate any output. The return value was unknown. The process exit code was -1066598274. The step failed.

Just wondering if anyone else has successfully configured their server to be able to use OPENROWSET to XLSX files and also have SSIS on the same server, Or does someone know if this configuration is not possible?

Edit: I should add that the failing packages are run from an SQL Agent job. When I connect to the server's Integration Services Manager via SQL Server Management Studio and run the package (by navigating to the package, right-clicking it and selecting Run Package), it executes successfully.

Blumenfeld

RUnnign it by right clicking in SSIS Manager and running it in SQL Agent are very different. The user account is different and the package run location is different. When you right click and run are you logged on to the server or are you on a remote client? Do you getting any 'failed to acquire connection' errors when it fails? Is this excel connection the only connection? – Less

What happens when you specify the DTEXEC command line in SQL Agent to use the 32-bit or 64-bit version? See also here for possible other means to choose which version executes. – Magnoliamagnoliaceous

@Nick.McDermaid "When you right click and run are you logged on to the server or are you on a remote client?" On a remote client. "Do you getting any 'failed to acquire connection' errors when it fails?" No. "Is this excel connection the only connection?" No. The problem is on the production server, we have a lot of connections there. – Simpleton

@BaconBits "Use 32-bit runtime" was already enabled before getting the error. – Simpleton

When you right click on a package and run it on the SSIS node in SSMS, you are running it on the client as yourself. This is why it works. I suggest you go back to the source and log into SSMS and run your OPENROWSET T-SQL directly in there. Any error?. If you are only using OPENROWSET to get data out of Excel then the DTExec bitness should be irrelevant, it's the SQL Server which is accesing the ACE driver, not SSIS – Less

Answer 1

Personally I've given up on trying to support this scenario - I think the "/passive" install is a hack that doesnt actually work and usually breaks your other install of ADE. I would unintall ADE entirely and then only re-install 32-bit. I would then force all packages run via SQL Agent to execute as 32-bit (as described by Brian).

Following this process gives you some hope of debugging issues as 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 there may be a performance hit but SSIS performance is typically limited to some extent by I/O or network speed, not just pure engine throughput.

Scarlett

Answer 2

Yes, it is possible. You have to specify to use the 32 bit runtime in the job step. Open every job step that runs one of your SSIS packages and click on the "Execution Options". Check the box at the bottom "Use 32 bit runtime".

Stout

I did it befor getting the error. Actually, "Use 32-bit runtime" resolves another error: SSIS DTS_E_OLEDB_EXCEL_NOT_SUPPORTED – Simpleton

Answer 3

I had "The step did not generate any output. The return value was unknown. The process exit code was -1066598274. The step failed" too.
I found a walk-around for myself: I switched from using xlsx to xls, and the error isn't raised anymore.

Simpleton

Answer 4

This is pretty old now but I did find a very simple solution for errors. The /passive seems to be causing issues for folks because there are multiple versions of the dtexec.exe and you just need to find the one that works. I did the following:

Each version of dtexec.exe corresponds to a specific version of SSIS. After upgrading SQL Server, it's crucial to ensure that you're using the version of dtexec.exe compatible with your SSIS packages. Here's how you can identify and use the correct version:

1. Navigate to the SQL Server Installation Directory: Typically, this is located in C:\Program Files (x86)\Microsoft SQL Server. Within this directory, you'll find subfolders named after different versions of SQL Server (e.g., 100, 110, 120, 130, 140, 150, and 160 for SQL Server 2022).

2. Locate dtexec.exe: Within each version's folder, navigate to the \DTS\Binn\ directory. Here, you'll find the dtexec.exe executable. The path will look something like this: C:\Program Files (x86)\Microsoft SQL Server\160\DTS\Binn\dtexec.exe.

3. Test Execution with the Correct dtexec.exe: Execute your SSIS package using the command line, specifying the full path to the correct version of dtexec.exe. For SQL Server 2022, this might be the one in the 160 folder at the command line:

   "C:\Program Files (x86)\Microsoft SQL Server\160\DTS\Binn\dtexec.exe" /file MyPackage.dtsx

Repeat this process with the dtexec.exe in different version folders if necessary until you find the one that works with your package.

Jesselton