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

Outfoot

Just to verify, add a new package to the project that will make it dreadfully obvious whether you're running in 32 or 64 bit mode – Auberge

Thanks for the link but I have to admit it is not dreadfully obvious. I am not sure what adding a new package tells me. Am I recreating my package? Sorry I don't follow. I also failed to point out I am running this in deployment model and it works fine from Visual Studio. – Outfoot

Sorry, tea hadn't kicked in yet. Yes, you clearly stated trying to run the package from the Integration Services catalog in SSMS There are a few different ways of running it from SSMS. Are you connected to the Integration Services Catalog (2012+) or are you using the SSMS connection type of Integration Services – Auberge

Integration Services Catalog (2012+) – Outfoot

Answer 1

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

Jacobson

It is installed and I set the option to false. – Outfoot

Also, aren't I running it on the server when I execute the package from the catalog? I can run the package from SSDT with no problem. – Outfoot

@Outfoot i provided new links in my answer. You can check them. I think this is what u are looking for – Jacobson

The first one is very useful – Jacobson

sqlblog.com/blogs/john_paul_cook/archive/2010/03/24/… – Jacobson

So the answer I got is that the Access 2010 engine was already installed and he installed the Access runtime. I am now getting a permissions error saying that either the file is opened exclusively or you do not have permission. I have permission to the folder and the file is not open by anyone. I am beginning to wonder if it is worth using Excel files. – Outfoot

You can try installing process monitor to checkout what is happening when executing the package – Jacobson

Check this links . They have a similar issue social.msdn.microsoft.com/Forums/sqlserver/en-US/… – Jacobson

dba.stackexchange.com/questions/81400/… – Jacobson

Thanks Hadi for the help. I appreciate it. Unfortunately, the link that is most similar to my problem is the first one and his solution was that he changed the path and forgot to change it back. The only other solution there was using process monitor to find if there is a lock. I have to contact someone else to do that since they took away our server access. The other solution is the proxy and correct me if I am wrong but it seems like when executing from the catalog it is executing as me (logging into SSMS with windows authentication). At least it shows me as the caller on the error report. – Outfoot

If you have the sa account try executing the package using dtexec using an sql authentication – Jacobson

Else plz give me a feedback if you get the solution – Jacobson

I can not run it with a SQL Server account. It must be run with a Windows Authentication account. I have seen several references to needing a proxy but I still don't understand that since it is executing with my account. Do you thin kit could have anything to do with the Excel file being on a remote share? I will modify my package and see if it works with the Excel file saved locally. – Outfoot

That worked. So it will not read the Excel file from a remote share. I suppose the proxy might fix that after all. – Outfoot

Happy to get things solved. If you find my answer helpful accept it else add your own answer – Jacobson

Answer 2

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.

Lavern

Not running from a job. Failing when I execute it from the catalog. It is executing fine from SSDT. – Outfoot

tried the second option above? try to turn 64BitRuntime from True to False in the Project properties within the solution tell me which architecture version you are using for sql server 32/64? on which the package is deployed – Lavern

Yes - you can see what is happening in my response to Hadi's post above. – Outfoot

In Visual Studio 2019, the option "64BitRuntime" is in the Solution Properties dialog, under debugging. – Cartilage

Answer 3

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

Auberge

FYI, He right clicked on the package in SSISDB in SSMS to get that GUI window – Periwinkle

@DanielL.VanDenBosch Good point that my answer assumed a level of proficiency with running a package. Do you find my edit addresses that or do I need to punch it up with additional details? – Auberge

yes that would be awesome. – Periwinkle

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.

Bishop

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.

Novocaine