SQL Clone
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
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18965 Visits: 20460
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
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27833 Visits: 13268
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Cindy Conway-312336
Cindy Conway-312336
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

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

Group: General Forum Members
Points: 32 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 Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 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
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27833 Visits: 13268
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27833 Visits: 13268
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Cindy Conway-312336
Cindy Conway-312336
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 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 Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 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