June 2, 2010 at 5:17 pm
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
June 2, 2010 at 5:55 pm
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[/url]
Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.
June 3, 2010 at 11:13 am
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
June 3, 2010 at 11:43 am
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[/url]
Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.
June 3, 2010 at 3:48 pm
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
October 26, 2010 at 11:55 am
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
October 26, 2010 at 11:58 am
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!
October 26, 2010 at 9:43 pm
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
October 31, 2010 at 4:07 pm
I'm having a similar problem:
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:
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”
February 15, 2011 at 4:30 pm
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.
April 4, 2011 at 12:09 pm
I believe there's an 32/64 bit option on SQL Agent 2008 to run DTSX in a job.
You can use the command line also and choose between dtexec.exe and dtexec32.exe
I believe BIDS works in 32 bits and theres an option to force 64 bits metioned in the reply before
April 4, 2011 at 7:35 pm
November 14, 2011 at 2:31 pm
Can I assume you followed the steps listed in this article?
http://blogs.msdn.com/b/farukcelik/archive/2010/06/04/accessing-excel-files-on-a-x64-machine.aspx
January 23, 2012 at 12:43 pm
I had the same problem, and after hours of debuggin. I just had my vendor send the file in .txt format and called it a day.
February 8, 2012 at 11:13 pm
I just experienced this issue, and although I was not able to get SSIS to talk to an Excel file directly on my 64-bit Windows Server OS (ACE 12.0 not registered errors), I was able to execute a SELECT * FROM OPENROWSET just fine. Therefore, I would propose a good work-around for this issue is to just create a view and then use an OLEDB source from within SSIS instead.
Something like this should do you fine:
CREATE VIEW [dbo].[view_OurWarehouseInventory] AS
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Data\Shared\WarehouseInventory.xlsx', [Inventory$])
GO
By the way, I hate SSIS. It is a bizarre application and every time I use it I feel like I'm poking my own eyes out with splintery chopsticks.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply