x

SSIS 32 Bit Driver Excel DriverOPENROWSET 64 Bit Excel Driver Cannot use both

OS: Windows Server 2008 R2 Enterprise 64 Bit

SQL:Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

BIDS (SSIS): Visual Studio 2008 Version 9 on .Net framework version 3.5

PROBLEM BACKGROUND

SSIS 2008 Excel 32 Bit Will Import Excel Source 64 Bit Driver Can use open rowset - Need to do both

I have downloaded the following drivers for Excel AccessDatabaseEngine_x64 (64 Bit) AccessDatabaseEngine (32 Bit)

I have ran the following configuration script:

    sp_configure 'show advanced options',1
    reconfigure
    GO
    sp_configure 'Ad Hoc Distributed Queries',1
    reconfigure 

The 32 bit drivers work with an Excel data source in SSIS. When I use OPENROWSET to export to Excel like following INSERT INTO OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 12.0;Database=C:\Export\testme.xlsx;','SELECT TestField FROM [Sheet1$]')
SELECT 'TestData'

I get this error:

Msg 7308, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Ace.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

I tried using JET:

    INSERT INTO OPENROWSET(‘Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;DatabaseDatabase=C:\Export\testme.xlsx;','SELECT TestField FROM [Sheet1$]')  
    SELECT 'TestData'

I get the following error

Msg 7308, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

I have tried this to thinking a different driver would work. With the 32 bit installed, I don’t see ACE ore JET as a provider. I do see MSDASQL so I thought this would work.

INSERT INTO OPENROWSET('MSDASQL','Driver={Microsoft Excel Driver (*.xls)};DBQ=D:\Export\Test.xls','SELECT Test FROM [Sheet1$]') SELECT 'A'

I get the following error

Msg 7399, Level 16, State 1, Line 2 The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 2 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)

When I switch to the 64 bit driver, OPENROWSET works in TSQL. However, the SSIS package fails with the following log entry.

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "MyConnection" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

QUESTION:

BIDS SSIS is a 32 bit application. SQL 2008 is a 64 bit application. The 32 bit drivers are working with SSIS but not TSQL. I need to export to Excel in TSQL and SSIS.

Does anyone know how to get openrowset (64 Bit) and SSIS Excel import (32 Bit) to work on the same server? I also tried developing my SSIS with the 32 bit drivers on a VM and deploying to the 64 bit server. I executed in 64 bit mode on the server and it still did not work.

more ▼

asked Apr 13, 2012 at 02:54 PM in Default

Rob 5 gravatar image

Rob 5
2 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
FYI a SQL Express 32 Bit installation is a good workaround however still looking for a solution to execute either 64 bit or 32 bit on the same SQL instance.,
more ▼

answered Apr 13, 2012 at 05:36 PM

Rob 5 gravatar image

Rob 5
2 1 1 1

(comments are locked)
10|1200 characters needed characters left

If you install 64 bit ACE drivers, then you should be able to read the excel using the OPENROWSET. You cannot use 32 bit ACE drivers to access excel on 64 bit SQL Server. YOu have to use 64 bit ACE drivers.

You can also check this blog post: [Dealing with Microsoft.Ace and OPENROWSET Errors][1]

For SSIS, you have to execute the SSIS package under 32bit version of dtexec.exe if running manually (located under c:\Program Files (x86)\.... or when running through Agent then don't forget to check Use 32 bit runtime in "Execution Options" tab of the SQL Server Integration Services Package job step.

[1]: http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/ace
more ▼

answered Apr 14, 2012 at 08:24 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x929
x115
x24
x11

asked: Apr 13, 2012 at 02:54 PM

Seen: 5146 times

Last Updated: Apr 14, 2012 at 08:24 PM