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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 5,317, Visits: 12,355
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1436319
Posted Thursday, March 28, 2013 3:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:57 AM
Points: 13,640, Visits: 11,511
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: Wednesday, December 10, 2014 8:20 AM
Points: 125, Visits: 495
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: Today @ 12:57 AM
Points: 13,640, Visits: 11,511
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: Today @ 12:57 AM
Points: 13,640, Visits: 11,511
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: Wednesday, December 10, 2014 8:20 AM
Points: 125, Visits: 495
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