Menu
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.
988
To check whether the 32-bit Microsoft Access Database Engine drivers are installed, go to C:\Windows\SysWOW64\odbcad32.exe. If on a 32-bit machine, go to C:\Windows\System32\odbcad32.exe to access the utility.
Description: Microsoft Access Database Engine technology allows for the communication and data exchange between files that are proprietary to the Microsoft Office package and other applications.
Open the Command Prompt by typing cmd in the Windows search box under the Start menu and selecting cmd.exe. Type the file path and file name of the 64-bit Access Database Engine 2010 or 2016 installation file, followed by a space and /quiet (this runs the installation without showing any messages).
SSIS and 64-bit Microsoft Access Database Engine. 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.
Now when connecting to .accdb databases, make sure you use the Microsoft Office 12.0 Access Database Engine OLE DB Provider. After selecting the new driver and pointing it to our database, click Test Connection, and now you get connection succeeded!!!! Now you can connect SSIS to the new Access .accdb format, welcome to the future.
Q: Why can't the 32 and 64 bit versions of the Microsoft Access Database Engine 2010 Redistributable (AccessDatabaseEngine.exe and AccessDatabaseEngine_X64.exe) both be installed on the same machine? A: Microsoft does not support the side by side install of 32 and 64-bit Microsoft Office 2010.
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.
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.
82
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
