March 28, 2013 at 2:31 am
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
March 28, 2013 at 2:54 am
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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 28, 2013 at 3:49 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 28, 2013 at 10:14 am
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
March 28, 2013 at 10:30 am
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
March 28, 2013 at 12:29 pm
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.
March 28, 2013 at 1:36 pm
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 28, 2013 at 1:38 pm
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 28, 2013 at 1:38 pm
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
April 2, 2013 at 12:40 pm
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply