Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL 2008 64 bit, SSIS & Excel (ACE OLE DB driver) Expand / Collapse
Author
Message
Posted Wednesday, June 2, 2010 5:17 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 24, 2013 4:31 PM
Points: 41, Visits: 132
Specs:
Windows Server 2003 R2 x64 SP2
SQL Server 2008 10.0.2531 (64 bit)
Visual Studio 2008 v9.0.30729.1 SP
.NET Framework v 3.5 SP1


I noticed that Microsoft finally came out with a 64 bit version of their ACE OLE DB driver for connection to Excel. I downloaded it from Microsoft downloads (Microsoft Access Database Engine 2010 Redistributable). I have several SSIS packages that need to output SQL data to Excel files (.xls & .xlsx). This works just fine in 32 bit mode using the 32 bit version of the download, but fails in 64 bit mode with the following error:

Error: 2010-06-02 19:12:44.11 Code: 0xC00F9304 Source: ExcelTest Connection manager "Excel 2007 Connection Manager" Description: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available. End Error Error: 2010-06-02 19:12:44.11 Code: 0xC020801C Source: Data Flow Task 2007 Excel 2007 Destination 1 [19] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel 2007 Connection Manager" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2010-06-02 19:12:44.11 Code: 0xC0047017 Source: Data Flow Task 2007 SSIS.Pipeline Description: component "Excel 2007 Destination 1" (19) failed validation and returned error code 0xC020801C. End Error

So, essentially, it doesn't recognize the 64 bit drivers as being installed. What's also maddening is that you can't install the 32 bit and 64 bit drivers side-by-side, so any troubleshooting in BIDS is completely useless. Had anyone else run into this?


________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
Post #931754
Posted Wednesday, June 2, 2010 5:55 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 22, 2010 9:44 AM
Points: 36, Visits: 109
Despite the fact that you thought you were using the 2010 driver - you weren't. Check your error message and you'll see it naming the 2007 driver... unless you've named everything oddly.

Check this post on the MSDN forums about troubleshooting the 2010 driver - it may help a bit: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/5a360a46-cf1e-45fa-8974-37caaaa5d3fc


Todd McDermid - SQL Server MVP, MCSD.Net, MCTS (SQL08BI)
Find great SSIS extensions - most are open source - at SSIS Community Tasks and Components
Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.
Post #931760
Posted Thursday, June 3, 2010 11:13 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 24, 2013 4:31 PM
Points: 41, Visits: 132
Thanks, Todd. You may be referring to me naming the connection "Excel 2007 Connection Manager", which may be a misnomer since I'm using the 2010 drivers.

I took a look at your link and it looks like everyone's having the same issue.

This is my connection string:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<filepath>;Extended Properties="Excel 12.0;HDR=YES";

Any manual editing of the string from Excel 12.0 to Excel 14.0 results in Extended Properties=Excel 8.0"Excel 14.0;HDR=YES"; There are ways around this using externalized configurations, expressions, and the like, but at run-time it gives me the same error. I also thought that the Provider portion of the string could use a change to Microsoft.ACE.OLEDB.14.0; but no luck. Basically, SQL doesn't recognize the driver as being installed.

As a side note, this machine doesn't have an Office suite installed, so the existing Excel connections are (ACE 12, Jet 8) are simply what come with SQL Server.


________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
Post #932307
Posted Thursday, June 3, 2010 11:43 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 22, 2010 9:44 AM
Points: 36, Visits: 109
So to be clear - you have this working in 32-bit mode through BIDS? If so, what's your connection string look like there? It doesn't have the "12" in it, does it? If it does, then you're not using the 2010 driver... so it's not really working in 32-bit mode.

The trick we're going to have to "learn" here is how to develop on 32-bit, then transition to QA/Prod on 64-bit... wish I had more time to work on that...


Todd McDermid - SQL Server MVP, MCSD.Net, MCTS (SQL08BI)
Find great SSIS extensions - most are open source - at SSIS Community Tasks and Components
Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.
Post #932318
Posted Thursday, June 3, 2010 3:48 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 24, 2013 4:31 PM
Points: 41, Visits: 132
The string looks the same (has the 12.0) in BIDS. Whenever I have the 32 bit driver installed, the SSIS package in BIDS validates just fine. Whenever I have the 64 bit installed (and 32 bit uninstalled, since they can't be installed side by side) it fails.

It appears to only recognize Microsoft Access Database Engine 2010 Redistributable 32 bit as version 12. Any manual editing of the connection string still fails with 32 bit mode. The following are not recognized at all:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<filepath>;Extended Properties="Excel 14.0;HDR=YES";
Provider=Microsoft.ACE.OLEDB.14.0;Data Source=<filepath>;Extended Properties="Excel 12.0;HDR=YES";
Provider=Microsoft.ACE.OLEDB.14.0;Data Source=<filepath>;Extended Properties="Excel 14.0;HDR=YES";


________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
Post #932466
Posted Tuesday, October 26, 2010 11:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 13, 2010 2:59 AM
Points: 19, Visits: 147
Dirt McStain (lovely name by the way ), does openrowset work for you using the ace driver? I've had exactly the same experience as you more or less and I made a workaround of adding an openrowset sql task in my packages. not ideal but it does work
Post #1011058
Posted Tuesday, October 26, 2010 11:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 13, 2010 2:59 AM
Points: 19, Visits: 147
The other funny thing is that the Import / Export wizard successfully sux up xl into a table but the ssis package it spawns wont realise the ace driver is installed on the box!
Post #1011061
Posted Tuesday, October 26, 2010 9:43 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 24, 2013 4:31 PM
Points: 41, Visits: 132
I've had openrowset do some very weird things when connecting to an Oracle database, so I've generally steered away from using it in general. Overall my conclusion is that the ACE 14 driver wasn't really meant to be a working solution for BIDS, but something that you can use with C# or within a script task. Maybe the next version of SQL Server will be able to use the driver properly.

________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
Post #1011243
Posted Sunday, October 31, 2010 4:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 14, 2012 2:46 PM
Points: 2, Visits: 89
I'm having a similar problem:
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/4349e61a-2570-4884-835d-3daa7aaea785

However, note that I believe that "12.0" is the correct way to refer to the v14 drivers in the connect string--they did not change that for the final release:
http://forums.devarticles.com/microsoft-access-development-49/the-microsoft-ace-oledb-14-0-provider-is-not-registered-238601.html
or see:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d
"If you are an application developer using OLEDB, set the Provider argument of the ConnectionString property to “Microsoft.ACE.OLEDB.12.0
Post #1013643
Posted Tuesday, February 15, 2011 4:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 30, 2014 6:54 PM
Points: 25, Visits: 108
I don't know if this makes a difference but I was able to suck in an Excel document using SQL 2008 R2's import wizard (OS is Win7 64 Bit) and I had it create a package on the file system. Since the Import Wizard was successful, I decided to create an SSIS solution and added the package that was created by the wizard to that solution in Business Intelligence Studio. When I ran that package from inside the BI IDE it failed with the error "The Microsoft ACE.OLEDB.12.0 provider is not registered on the local machine" and stating that the 64 bit driver may not be installed. I wondered then how the SQL Server Import wizard was able to get it to work but not in SSIS. Well I ended up clicking the menu option Project, Properties, then in the dialog box under Configuration Properties, Debugging, Debug Options, I set the option Run64BitRuntime to false. Then when I executed from BI, it ran fine.
Post #1064629
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse