Search code examples
ssis-2012

SSIS Load data from Excel .xlsx without installing 32 bit driver

I need to load data from excel with .xlsx extension to Netezza table. I get following error. The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode.

Is there any way to use excel with .xlsx extension without installing 32- bit driver?

Solution

  • Change the project property Run64BitRuntime to True.

    enter image description here

    More information can be found in Microsoft's article - 64 bit Considerations for Integration Services.

    When you run a package in 64-bit mode, you might not be able to connect to as many data sources as you can when you run a package in 32-bit mode. Some .NET Framework Data Providers and native OLE DB providers might not be available in 64-bit versions. For example, the Microsoft OLE DB Provider for Jet, which connects to Access databases and Excel spreadsheets, is not available in a 64-bit version. Also, the SQL Server Compact Provider, which connects to SQL Server Compact data sources, is not available in a 64-bit version.

    The ACE Provider and its Excel 2010 driver (64-bit, 32-bit) can be used with Integration Services under certain circumstances. For more information and to download the provider, see Microsoft Access Database Engine 2010 Redistributable.

    The 32-bit SSIS Designer displays only 32-bit providers that are installed on the local computer. To configure a connection manager to use a specific 64-bit provider, you must install the 32-bit version of the provider on the development computer for use at design time. Even though the 32-bit version of the provider is installed, you can still run the package in 64-bit mode, both in the development environment and after deployment. The 32-bit and 64-bit versions of a provider have the same ID. Therefore, the SSIS runtime will select the appropriate version of the provider to use. For example, you run the package in the development environment on a 64-bit computer. By default, the package uses the 64-bit version of the provider because the default value of the Run64BitRuntime project property is True.