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

64-bit driver for Excel Expand / Collapse
Author
Message
Posted Thursday, March 28, 2013 2:31 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 9:52 PM
Points: 8, Visits: 88
My understanding is that the file structure for the 64bit and 32bit versions of Excel are the same in Office 2010. See: http://answers.microsoft.com/en-us/office/forum/office_2010-excel/can-excel-2010-64bit-files-be-saved-in-a-format-to/1c5d8522-ef5d-4eab-bf4b-1efa861b434a).

That being the case, why doesn't the 64-bit version of the drivers (microsoft.ACE.OLEDB.12.0) work for creating an Excel connection in SSIS for a file created with the 32-bit version of Office 2010.

I've loaded the 32-bit version and things run fine, but it's just bugging me that I don't understand this.


www.sqlwithcindy.com
Post #1436310
Posted Thursday, March 28, 2013 2:54 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
Cindy Conway-312336 (3/28/2013)
My understanding is that the file structure for the 64bit and 32bit versions of Excel are the same in Office 2010. See: http://answers.microsoft.com/en-us/office/forum/office_2010-excel/can-excel-2010-64bit-files-be-saved-in-a-format-to/1c5d8522-ef5d-4eab-bf4b-1efa861b434a).

That being the case, why doesn't the 64-bit version of the drivers (microsoft.ACE.OLEDB.12.0) work for creating an Excel connection in SSIS for a file created with the 32-bit version of Office 2010.

I've loaded the 32-bit version and things run fine, but it's just bugging me that I don't understand this.


Are you saying that if the file is created with a 64-bit version of Excel, everything works fine?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1436319
Posted Thursday, March 28, 2013 3:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:20 PM
Points: 13,252, Visits: 10,133
Cindy Conway-312336 (3/28/2013)
My understanding is that the file structure for the 64bit and 32bit versions of Excel are the same in Office 2010. See: http://answers.microsoft.com/en-us/office/forum/office_2010-excel/can-excel-2010-64bit-files-be-saved-in-a-format-to/1c5d8522-ef5d-4eab-bf4b-1efa861b434a).

That being the case, why doesn't the 64-bit version of the drivers (microsoft.ACE.OLEDB.12.0) work for creating an Excel connection in SSIS for a file created with the 32-bit version of Office 2010.

I've loaded the 32-bit version and things run fine, but it's just bugging me that I don't understand this.


Because the 64-bit drivers aren't supported in BIDS/SSDT, because it is a 32-bit application.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1436348
Posted Thursday, March 28, 2013 10:14 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 9:52 PM
Points: 8, Visits: 88
Of course. That makes sense. I'm using 2012, so it's Visual Studio 2010/SSDT, but still that is 32-bit.

Thank you!


www.sqlwithcindy.com
Post #1436594
Posted Thursday, March 28, 2013 10:30 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 9:52 PM
Points: 8, Visits: 88
A follow-up question ...

If VS is running as 32-bit, why do I need to set Run64BitRuntime to False? If I don't I get the following error:

The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode...

My Environment
Windows 8
VS 2010 shell
SSDT
Excel 2007 Workbook

Thanks!


www.sqlwithcindy.com
Post #1436606
Posted Thursday, March 28, 2013 12:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:38 PM
Points: 124, Visits: 486
Cindy Conway-312336 (3/28/2013)
A follow-up question ...

If VS is running as 32-bit, why do I need to set Run64BitRuntime to False? If I don't I get the following error:

The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode...

My Environment
Windows 8
VS 2010 shell
SSDT
Excel 2007 Workbook

Thanks!


That setting is for when you run your package from the SQL Server Job Agent, which runs using 64-bit.
Post #1436653
Posted Thursday, March 28, 2013 1:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:20 PM
Points: 13,252, Visits: 10,133
Cindy Conway-312336 (3/28/2013)
A follow-up question ...

If VS is running as 32-bit, why do I need to set Run64BitRuntime to False? If I don't I get the following error:

The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode...

My Environment
Windows 8
VS 2010 shell
SSDT
Excel 2007 Workbook

Thanks!


Not 100% sure, but I think the debugging environment (not the same as the design environment) mimics a 64-bit environment. If a 64-bit provider is not present - such as the ACE OLE DB provider most of the time - you need to set the Run64BitRuntime property to false.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1436696
Posted Thursday, March 28, 2013 1:38 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:20 PM
Points: 13,252, Visits: 10,133
tmitchelar (3/28/2013)
Cindy Conway-312336 (3/28/2013)
A follow-up question ...

If VS is running as 32-bit, why do I need to set Run64BitRuntime to False? If I don't I get the following error:

The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode...

My Environment
Windows 8
VS 2010 shell
SSDT
Excel 2007 Workbook

Thanks!


That setting is for when you run your package from the SQL Server Job Agent, which runs using 64-bit.


No. The Run64BitRuntime property is a design time property only.
You have a similar setting in the SQL Server Agent job, but only when you have a 64-bit installation of SQL Server and SSIS which runs the packages by default in 64-bit.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1436699
Posted Thursday, March 28, 2013 1:38 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 9:52 PM
Points: 8, Visits: 88
Thanks everyone for your replies!

Here is what I have found. If I have just the 32-bit drivers installed, I need to set the Run64BitRuntime to False, or I get an error in the development environment at runtime. If I have both the 32-bit AND 64-bit drivers installed, I can leave Run64BitRuntime = True. Keep in mind I am using a 64-bit operating system.

BIDS on 64-bit OS
http://sqlwithcindy.blogspot.com/2013/03/loading-excel-2007-file-in-ssis-2012-on.html


BIDS on 32-bit OS
http://sqlwithcindy.blogspot.com/2013/03/loading-excel-2007-file-in-ssis-2012.html


www.sqlwithcindy.com
Post #1436700
Posted Tuesday, April 2, 2013 12:40 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:38 PM
Points: 124, Visits: 486
Koen Verbeeck (3/28/2013)
tmitchelar (3/28/2013)
Cindy Conway-312336 (3/28/2013)
A follow-up question ...

If VS is running as 32-bit, why do I need to set Run64BitRuntime to False? If I don't I get the following error:

The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode...

My Environment
Windows 8
VS 2010 shell
SSDT
Excel 2007 Workbook

Thanks!


That setting is for when you run your package from the SQL Server Job Agent, which runs using 64-bit.


No. The Run64BitRuntime property is a design time property only.
You have a similar setting in the SQL Server Agent job, but only when you have a 64-bit installation of SQL Server and SSIS which runs the packages by default in 64-bit.


Good catch and thank you for the correction. I generally try to avoid Excel files in SSIS and try to push towards using .csv files for a variety of reasons - this being one of them along with some "pull your hair out" formatting issues.
Post #1438056
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse