December 3, 2012 at 9:28 pm
Comments posted to this topic are about the item Using SSIS to export data from a 64 bit server to Excel 2007
December 4, 2012 at 12:58 am
As far as I know, the format of files generated by either the 32-bit version or the 64-bit version of Excel is exactly the same - so referring to '32-bit files' is a little misleading. It's the method of connecting to the file that needs to be 32-bit.
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
December 4, 2012 at 1:06 am
I had same experience as Krista Olson. You need 32-bit Microsoft Access Database engine 2010 to get it work. Not from the reason that excel files are 32-bit or 64-bit, the problem is in OS which is not 64-bit completely. For example if you schedule the job in Agent - Agent is running as a 32-bit process and cannot run any 64-bit dtsx package.
December 4, 2012 at 2:26 am
This is misleading
When done creating and testing the SSIS package on your workstation, be sure to change in Solution Explorer the Debugging properties back to Run64BitRuntime to true prior to running or scheduling it to run from a 64 bit server.
As:
"The Run64BitRuntime project property applies only at design time."
http://msdn.microsoft.com/en-us/library/ms141766(v=sql.105).aspx
December 4, 2012 at 4:03 am
I had tried this a while ago. This messes up the use of office applications.
When you launch any of the office applications, it starts to install the software.
Am I the only one? Has anyone experienced this?
December 4, 2012 at 6:29 am
inmallinath (12/4/2012)
I had tried this a while ago. This messes up the use of office applications.When you launch any of the office applications, it starts to install the software.
Am I the only one? Has anyone experienced this?
I don't remember ever having this problem.
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
December 4, 2012 at 6:46 am
What timing! I just went through the agony of getting the Excel connection to work on a 64 bit machine when running a SSIS package via SQL Server agent. My solution was probably the same as previously recommended by others: Run the package as Operating System (CmdExec), point to the 32 bit DTExec.exe in the command line.
Does the method outlined by Kirsta also work for Excel 2003?
December 4, 2012 at 8:12 am
Really appreciate the article, hope SQL Server Central will consider many more articles like this one.
Could SQL Server Central please create a major category for SQL / Excel with the sub folders?
Excellent article, that only scratches the surface.
As a consultant to an international organization, I provide Excel 2010 reports that include Excel Object programming distributed with Citrix.
The use of SQL Server, Excel Objects, and Access 2010 run-time is extremely powerful for custom real-time analysis.
When groups of engineers, accounting, and compliance meet, they can perform "what-if" decision processes dynamically with Excel, a tool they know.
Rich User Interfaces such as Excel instead of HTML post increases worker efficiency. Data-Mining SQL Server with Excel is very powerful.
Users provided with selected views of Excel can work with the results more efficiently and cost effectively.
The speed of a seasoned Excel Object Model Programmer to deliver complex Business-Rule-Based decision tools over SharePoint is another consideration.
SharePoint and SSRS are very efficient for high-volume standard Internet-wide uses.
There is still a need for small-business or business units to utilize rich interface tools such as Excel for critical and timely business rule based decision process.
December 4, 2012 at 1:03 pm
Great job Krista, I appreciate you taking the time to write this up as an article. Excel with and without PowerPivot connections is a familiar and flexible tool for my end users. I find that we are using the Report Manager (SSRS) less these days and relying more on direct connections between our rendering db and Excel. I had also cobbled together a similar solution by trial and error since upgrading to SQL 2008 R2 a couple of months ago.
Thanks sharing what you discovered with others, you have probably saved serveral readers out there a great deal of frustration.
December 5, 2012 at 2:39 am
There are some flaws in this article:
* Run64BitRuntime is a design-time debugging property, which has nothing to do with packages running on a server.
* Why would you want to install the x86 ACE OLE DB provider on your server? As Phil said, there's no difference between an Excel file created by 32-bit Excel or 64-bit Excel.
The only thing this article describes is how you can create the package, because BIDS is a 32-bit application.
For example if you schedule the job in Agent - Agent is running as a 32-bit process and cannot run any 64-bit dtsx package.
Agent is not a 32-bit process if SQL Server is 64-bit. If you do have a 64-bit SQL Server, SSIS packages scheduled with Agent run in 64-bit, unless you explicitly specify that 32-bit has to be used.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 5, 2012 at 2:49 am
Koen Verbeeck (12/5/2012)
Why would you want to install the x86 ACE OLE DB provider on your server? As Phil said, there's no difference between an Excel file created by 32-bit Excel or 64-bit Excel.
The only reason i can think of to install the ACE providers is if you want to deal with xlsx files. As soon as you do anything with an xlsx file in SSIS it uses the ACE providers and then you would need it on the server.
Dan
December 5, 2012 at 3:14 am
danielfountain (12/5/2012)
Koen Verbeeck (12/5/2012)
Why would you want to install the x86 ACE OLE DB provider on your server? As Phil said, there's no difference between an Excel file created by 32-bit Excel or 64-bit Excel.The only reason i can think of to install the ACE providers is if you want to deal with xlsx files. As soon as you do anything with an xlsx file in SSIS it uses the ACE providers and then you would need it on the server.
Dan
Yes, but why install the 32-bit version on a 64-bit server? Just install the 64-bit one.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 5, 2012 at 3:16 am
Koen Verbeeck (12/5/2012)
danielfountain (12/5/2012)
Koen Verbeeck (12/5/2012)
Why would you want to install the x86 ACE OLE DB provider on your server? As Phil said, there's no difference between an Excel file created by 32-bit Excel or 64-bit Excel.The only reason i can think of to install the ACE providers is if you want to deal with xlsx files. As soon as you do anything with an xlsx file in SSIS it uses the ACE providers and then you would need it on the server.
Dan
Yes, but why install the 32-bit version on a 64-bit server? Just install the 64-bit one.
Very True.
December 5, 2012 at 3:46 am
danielfountain (12/5/2012)
Koen Verbeeck (12/5/2012)
danielfountain (12/5/2012)
Koen Verbeeck (12/5/2012)
Why would you want to install the x86 ACE OLE DB provider on your server? As Phil said, there's no difference between an Excel file created by 32-bit Excel or 64-bit Excel.The only reason i can think of to install the ACE providers is if you want to deal with xlsx files. As soon as you do anything with an xlsx file in SSIS it uses the ACE providers and then you would need it on the server.
Dan
Yes, but why install the 32-bit version on a 64-bit server? Just install the 64-bit one.
Very True.
The reason should be the development process itself (Business intelligence development studio). Here is the explanation from Faruk Celik:
Visual Studio 2010 (or 2008, 2005) IDE itself is a 32bit process named "devenv.exe" (You can check from "Task Manager", you will "*32" next to devenv.exe like "devenv.exe *32"). We don't have 64bit version of Visual Studio 2010 (or the old ones), it is/was always 32bit.
As devenv.exe (Visual Studio 2010 for your scenario) is a 32bit process, it cannot reach 64bit 'Microsoft.ACE.OLEDB.12.0' OLEDB Provider or any 64bit OLEDB provider at all.
So, you have to have 32bit version of 'Microsoft.ACE.OLEDB.12.0' OLEDB Provider.
There is more about the topic in this MSDN blog:
Very interesting is the option, that allows you install both 'Microsoft.ACE.OLEDB.12.0' providers (32bit and 64bit) at once: first install x86 normally, then run AccessDatabaseEngine_X64.exe /passive (otherwise you get message that 32bit is already on the machine).
PS: sorry for my previous post about Job Agent, that was bullshit.
December 5, 2012 at 3:51 am
x_t_r (12/5/2012)
danielfountain (12/5/2012)
Koen Verbeeck (12/5/2012)
danielfountain (12/5/2012)
Koen Verbeeck (12/5/2012)
Why would you want to install the x86 ACE OLE DB provider on your server? As Phil said, there's no difference between an Excel file created by 32-bit Excel or 64-bit Excel.The only reason i can think of to install the ACE providers is if you want to deal with xlsx files. As soon as you do anything with an xlsx file in SSIS it uses the ACE providers and then you would need it on the server.
Dan
Yes, but why install the 32-bit version on a 64-bit server? Just install the 64-bit one.
Very True.
The reason should be the development process itself (Business intelligence development studio). Here is the explanation from Faruk Celik:
Visual Studio 2010 (or 2008, 2005) IDE itself is a 32bit process named "devenv.exe" (You can check from "Task Manager", you will "*32" next to devenv.exe like "devenv.exe *32"). We don't have 64bit version of Visual Studio 2010 (or the old ones), it is/was always 32bit.
As devenv.exe (Visual Studio 2010 for your scenario) is a 32bit process, it cannot reach 64bit 'Microsoft.ACE.OLEDB.12.0' OLEDB Provider or any 64bit OLEDB provider at all.
So, you have to have 32bit version of 'Microsoft.ACE.OLEDB.12.0' OLEDB Provider.
There is more about the topic in this MSDN blog:
Very interesting is the option, that allows you install both 'Microsoft.ACE.OLEDB.12.0' providers (32bit and 64bit) at once: first install x86 normally, then run AccessDatabaseEngine_X64.exe /passive (otherwise you get message that 32bit is already on the machine).
PS: sorry for my previous post about Job Agent, that was bullshit.
Sorry i am confused here. Are you talking about developing on a server rather then a client? If you are trying to dev on a server then you may need it.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply