SSIS why all connection manager drivers pointing to 32 bit versions?

Question

I was struggling for long time to export data to Excel while running my package in 64-bit mode. Currently I have set Runtime64bit to false to get job done but I really want to run in 64-bit mode for some strong reasons.

For that I have installed AccessDatabaseEngine_X64.exe (after uninstalling existing drivers). But I still get unable to acquire connection error. I have to run in 32 bit mode even after installing 64-bit driver. What is wrong?

What I have noticed is that when I creating a new Excel connection the connection manager dialog box is showing the drivers path pointing to 32 bit version. When I looked at other drivers, they are also pointing to 32-bit version. (see screenshot below). Is there anything to do with this?

My Environment: - Windows Server 2012 Standard (64-bit) - MSSS DT 2012 - MS Excel 2010 (64-bit) - MicrosfotAccess Data Engine 2010 (64-bit)

My Excel file is saved in 97-2003 format (.xls)

Answer 1

Let me quote this FAQ - How to run SSIS Packages using 32-bit drivers on 64-bit machine

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.

-- EDIT --

Extended explanation by example.

Imagine you create a new SSIS package. In it you connect to an Excel file. For this to work, you need to have MS Office, or the Microsoft Access 2016 Runtime in the machine that is executing the package.

So, you are creating the package in VS in your desktop. You have Office 32 bit installed and it all works fine.

When you deploy to the serve, in this case a Windows 2012 (64 bit). You think that... STOP! The bit of the server does NOT matter. OK, but my SQL Server is 64 bit so... NO, it does not matter neither!

Once an SSIS package is published, look at the scheduled job properties. In there you can specify to run in 64 or 32 bit mode.

Depending on this you need to have the correct version of drivers installed!

Run the SSIS package on 64 bit; install 64 bit drivers!

Run it on 32 bit; then install the 32 bit drivers!

Answer 2

But I still get unable to acquire connection error. I have to run in 32 bit mode even after installing 64-bit driver. What is wrong?

When you execute your package and look at the Progress tab, you will no doubt be getting an error message similar to:

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

When you set up your Excel Connection Manager, choosing Excel 97-2003 file type will default to the Microsoft Jet OLEDB driver which is available as a 32-bit version only.

Assuming you have the 64-bit Microsoft Access Database Engine 2010 Redistributable still installed, what you need to do is click on the Excel Connection Manager you created in the Connection Managers tab. In the Properties pane, look for the ConnectionString property (under Misc if grouped by category).

You'll see the Provider is set as Provider=Microsoft.Jet.OLEDB.4.0, the 32-bit only driver. If you had chosen "Excel 2007" as the file type it would have used Microsoft.ACE.OLEDB.12.0 which is 32-bit or 64-bit depending on which Microsoft Access Database Engine Redistributable you installed.

Change the Provider to Microsoft.ACE.OLEDB.12.0 in the ConnectionString property. When you change this, you'll notice errors appear in the Error List pane similar to:

Error 1 Validation error. Data Flow Task 1: Package: 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. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". Package.dtsx 0 0

Error 2 Validation error. Data Flow Task 1: Package: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. For more information, see http://go.microsoft.com/fwlink/?LinkId=219816 Package.dtsx 0 0

These errors are related to SSDT and a point you made earlier which was:

When I looked at other drivers, they are also pointing to 32-bit version. (see screenshot below). Is there anything to do with this?

SQL Server Data Tools is a 32-bit application and is likely the reason why in the "Add SSIS Connection Manager" dialog you are seeing the Connection Managers pointing to 32-bit versions. Requests for 64-bit SSDT have been made quite some time ago. It also the reason these new errors are appearing pre-execution and in a pop-up message box if you attempt to execute your package.

Notice the errors are validation errors which hints at the solution.

Select your Excel Connection Manager, set the DelayValidation property to False. This will stop pre-execution errors showing. Secondly, either for the Package or for the Control Flow task that uses your Excel Connection Manager, set the DelayValidation property to False. This allows you to run the package and stop the errors at runtime.

Answer 3

Check if you have both versions of the DtsDebugHost.exe installed.

If you do you might need to install the latest service pack for Windows Server 2012.

reference