Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


64-bit driver for Excel


64-bit driver for Excel

Author
Message
Cindy Conway-312336
Cindy Conway-312336
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: 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
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8381 Visits: 19501
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16467 Visits: 13207
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Cindy Conway-312336
Cindy Conway-312336
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: 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
Cindy Conway-312336
Cindy Conway-312336
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: 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
tmitchelar
tmitchelar
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 508
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.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16467 Visits: 13207
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16467 Visits: 13207
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Cindy Conway-312336
Cindy Conway-312336
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: 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
tmitchelar
tmitchelar
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 508
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.
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