SSIS Error: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered.

Error:

[Connection manager “Excel Connection Manager”] Error: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.
An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x80040154 Description: “Class not registered”.

Solution:

Running 32-bit SSIS in a 64-bit Environment

On 64 Operating System when you install Integration Services it will install 32-Bit and 64-Bit version of DTExec commandline tool which is used to execute SSIS packages.

DTExec 32-Bit  can be found under  : C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn

DTExec 64-Bit  can be found under  : C:\Program Files\Microsoft SQL Server\90\DTS\Binn

For more information click on the following URL

http://msdn.microsoft.com/en-us/library/ms162810.aspx

If your SSIS package is referencing any 32-Bit DLL or 32-Bit drivers from your package then you must use 32-Bit version of DTExec to execute SSIS package.

Common example of 32-Bit driver is : Microsoft Jet Driver (MS Access or Excel). There is no 64-Bit version of Jet Driver so any packages using Jet driver (i.e. Excel or MS Access Database Connections) must be called using 32 bit DTExec.

When you are designing such SSIS packages in Visual Studio and if you have reference to any 32-Bit driver/dll then make sure you change  Project Property Run64BitRuntime to False before you Debug your package in BIDS otherwise your package will try to load 64-Bit dlls instead of 32-Bit.

To change this setting

– Right click on Project Node–> Properties

– Configuration properties tab–> Under Debugging option change Run64BitRuntime to False

-After following above steps remove the already created connection manager and create the new connections to avoid trauma.

image_25DA0062

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s