SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL 2008 64 bit, SSIS & Excel (ACE OLE DB driver)


SQL 2008 64 bit, SSIS & Excel (ACE OLE DB driver)

Author
Message
Dirt McStain
Dirt McStain
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 138
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
Todasdasdfd asfasf
Todasdasdfd asfasf
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 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.
Dirt McStain
Dirt McStain
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 138
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
Todasdasdfd asfasf
Todasdasdfd asfasf
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 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.
Dirt McStain
Dirt McStain
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 138
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
nzrdb6
nzrdb6
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 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
nzrdb6
nzrdb6
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 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!
Dirt McStain
Dirt McStain
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 138
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
TechVsLife
TechVsLife
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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
SQLKicksButt
SQLKicksButt
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 145
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search