SSIS – can’t create connection manager to Access DB

user_6436542 0
Viewed 235

I need to pull data from an Access .accdb database and put it into a SQL Server table. I am unable to create a connection to the Access database from an SSIS package.

  • Visual Studio 2010 Pro - development tool.
  • SQL Server Management Studio 2012 - database tool.
  • Windows 10 Enterprise – OS on development PC.
  • Office 365, 64-bit – productivity suite on development PC.

Several articles state to use the “Microsoft Office 12.0 Access Database Engine OLE DB Provider” to create a connection to the Access database. I currently do not have this data provider.

The 2010 version of Visual Studio is a 32-bit product, and therefore needs a 32-bit connection to Access. I have tried to install the “Microsoft Access Database Engine 2010 Redistributable”, but it will not install because I already have the 64-bit version of Access installed.

Any thoughts on how to install the 32-bit Access data provider – in the presence of a 64-bit version of Access.

etl oledb ms-access ssis visual-studio
1 Answers

To install Microsoft Access database engine 32-bit alongside 64-bit installation, you should install it using the passive mode.

Passive mode installation steps

  1. Open the Command Prompt by typing cmd in the Windows search box under the Start menu and selecting cmd.exe

  2. Type the file path and file name of the 32-bit Access Database Engine 2010 install file, followed by a space and /passive (this runs the installation without showing any error messages).

  3. Open the Registry Editor by typing regedit in the Windows search box under the Start menu and selecting regedit.exe

  4. Delete or rename the mso.dll registry value in the following registry key:

     "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Common\FilesPaths"

More details and screenshots are found in the link below

user_70312300
Related