SSIS package fails with error "If 64-bit driver not installed, run in 32-bit mode"

Question

I am receiving the following error when trying to run the package from the Integration Services catalog in SSMS. I changed the 64BitRuntime option to FALSE but it still does not work. The error below is followed by an error that a connection cannot be made to my Excel connection manager. Any suggestions?

Package 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

Answer 1

if you are executing the SSIS package from job , there is an option in job configuration a checkbox "enable 32 bit".

OR

if you are executing the SSIS package from BIDS or SSDT , go to project properties=> Configuration => debugging => turn 64BitRuntime from "True" to "False" as it is set to True by default.

Answer 2

You are attempting to run an SSIS package from the SSISDB catalog and need it to be in 32 bit mode.

The TSQL for such would look like the following

DECLARE @execution_id bigint;
EXEC SSISDB.catalog.create_execution
    @package_name = N'Legacy_DataExport.dtsx'
,   @execution_id = @execution_id OUTPUT
,   @folder_name = N'Legacy_DataExport'
,   @project_name = N'Legacy_DataExport'
,   @use32bitruntime = True
,   @reference_id = NULL;
SELECT
    @execution_id;
DECLARE @var0 smallint = 1;
EXEC SSISDB.catalog.set_execution_parameter_value
    @execution_id
,   @object_type = 50
,   @parameter_name = N'LOGGING_LEVEL'
,   @parameter_value = @var0;
EXEC SSISDB.catalog.start_execution @execution_id;
GO

Of note is the penultimate parameter of the first EXEC where we specify @use32bitruntime = True

That says, please run the package Legacy_DataExport.dtsx which can be found in the project Legacy_DataExport which can be found in the folder Legacy_DataExport using the 32bit runtime.

From the UI perspective, it looks like

The click path within SSMS for this would be

• Expand the "Integration Services Catalogs" node under "Management"
• Expand the only option there of "SSISDB"
• Expand the Folder where your project exists - "Legacy_DataExport" in my case
• Expand the "Projects" node
• Expand your actual project node - my project is also called "Legacy_DataExport"
• Expand "Packages"
• Find your package, again my example is "Legacy_DataExport.dtsx", right click it and select Execute...

Answer 3

You have to install Microsoft Access Database Engine 2010 Redistributable and
set 64BitRuntime option to FALSE

you can get it from the following link:

• https://www.microsoft.com/en-us/download/details.aspx?id=13255

More info and details can be found in the following links:

• http://sqlblog.com/blogs/john_paul_cook/archive/2010/03/24/running-32-bit-ssis-in-a-64-bit-environment.aspx
• http://toddmcdermid.blogspot.com/2009/10/quick-reference-ssis-in-32-and-64-bits.html?m=1
• https://msdn.microsoft.com/en-us/library/ms162810.aspx

Answer 4

I have faced same issue when I try with the existing SSIS dtsx packages on VS 2019.

Solution: I have removed existing OLEDB connections from the connection manager and created newly from the scratch. It resolved the issue.

Answer 5

Using the built in excel connection manager in SSIS, the package needs to run in 32-bit mode. Switching this:

64BitRuntime option to FALSE

Only allows SSDT to run the package in 32bit mode, but it does not affect how it will run once you deploy it. To run it in 32bit mode from SSMS:

• If you are right clicking on the package in the Integration Services Catalog and hitting execute, go to the advanced tab of the dialogue and check 32-bit runtime.
• If you are executing it via a SQL Agent job. In the step, go to configuration > Advanced and check 32-bit runtime.