CODEWITHSUNDEEP
sql-serverssisetl
Scottdg
Scottdg

Reputation: 113

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

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

Upvotes: 5

Views: 34063

Answers (5)

user21309895
user21309895

Reputation: 1

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.

Upvotes: 0

billinkc
billinkc

Reputation: 61211

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

enter image description here

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...

Upvotes: 3

Hadi
Hadi

Reputation: 37313

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

you can get it from the following link:

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

Upvotes: 2

fahad
fahad

Reputation: 164

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.

Upvotes: 5

Mark Wojciechowicz
Mark Wojciechowicz

Reputation: 4477

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.

Upvotes: -1

Related Questions