December 5, 2012 at 4:35 am
danielfountain (12/5/2012)
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.
Usually you are facing problem with 'Microsoft.ACE.OLEDB.12.0' two times:
1) development
- 'Microsoft.ACE.OLEDB.12.0' makes the connection between SSIS and Excel, so it is needed on any machine you are building your SSIS package (doesnt matter whether server or client)
- 32bit BIDS cannot make connection to xlsx file through 64bit 'Microsoft.ACE.OLEDB.12.0' provider - you will receive message that provider is not registered on the machine
- so the only option I know is to build SSIS package 32bit using 32bit ACE
2) production
- usualy there is no Microsoft Office installed on the server
- you have to install 'Microsoft.ACE.OLEDB.12.0' which is required by your package
- you have to install 32bit 'Microsoft.ACE.OLEDB.12.0' because your package is 32bit
So I completely agree with Krista Olson, that only 32bit 'Microsoft.ACE.OLEDB.12.0' works well and my question is why to install 64bit one and how to build SSIS package that can use 64bit 'Microsoft.ACE.OLEDB.12.0'?
December 5, 2012 at 4:52 am
If you don't specify the package to run in 32-bit it will run in 64-bit and thus search for 64-bit providers.
So I think you won't need the 32-bit provider on the server. Haven't tested it myself though.
By the way, installing 32-bit and 64-bit side by side isn't possible if you have Office installed. Very frustrating...
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 7:07 am
Hi
Sorry to disillusion you, but the 2010 redistributable won't install if you have 64 bit office installed.
What you need is the 32 bit version of the AccessDatabaseEngine from 2007.
a bit hard to find, so here is a link to my copy:
And this, as long as you run it *after* running your 64 bit office install, will happily run in parallel.
(Probably not needed for production, but essential for an all-in dev box)
December 5, 2012 at 8:00 am
I tried this method this morning, but I must be missing something.
We have SQL Server 2005 running on a 64 bit server. I loaded Microsoft Access Database engine 2010 to the server and changed an existing Excel output connection in an SSIS package to an OLE DB output connection using Microsoft Office 12.0 Access Database Engine OLE DB Provider with the extended property set to Excel 12.0. Everything ran fine in Visual Studio.
I then set up a SQL Server agent job to run the package. But I got the same errors I got before I changed another job to use Operating System (CmdExec), pointing to the 32 bit DTExec.exe in the command line: Class Not Registered.
Granted, I'm using Excel 2003 as a destination, and maybe that screws things up. Maybe I can't use Excel 12.0. So, is my problem that I'm using SQL Server 2005, or is my problem that I'm using Excel 2003 as a destination (and no, I can't change that to another version of Excel)? Or, does the method outlined in the article not address the "class not registered" problems I'm having with SQL Server agent jobs using Excel?
December 5, 2012 at 8:22 am
rburko (12/5/2012)
I tried this method this morning, but I must be missing something.We have SQL Server 2005 running on a 64 bit server. I loaded Microsoft Access Database engine 2010 to the server and changed an existing Excel output connection in an SSIS package to an OLE DB output connection using Microsoft Office 12.0 Access Database Engine OLE DB Provider with the extended property set to Excel 12.0. Everything ran fine in Visual Studio.
I then set up a SQL Server agent job to run the package. But I got the same errors I got before I changed another job to use Operating System (CmdExec), pointing to the 32 bit DTExec.exe in the command line: Class Not Registered.
Granted, I'm using Excel 2003 as a destination, and maybe that screws things up. Maybe I can't use Excel 12.0. So, is my problem that I'm using SQL Server 2005, or is my problem that I'm using Excel 2003 as a destination (and no, I can't change that to another version of Excel)? Or, does the method outlined in the article not address the "class not registered" problems I'm having with SQL Server agent jobs using Excel?
You did not state which version of the Access 2010 provider you have installed on the server.
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 5, 2012 at 8:23 am
Good point. 32 bit.
December 5, 2012 at 8:32 am
You did not state which version of the Access 2010 provider you have installed on the server.
Good point. 32 bit version
December 5, 2012 at 8:38 pm
Thank you all for the good discussion and the positive comments - my first attempt at technical writing published at a larger scale. Hopefully now to some answers: When I used to have an export to Excel 2003 I had to install a completely different set of Access database engine files on the server for the Excel 2003 SSIS. I will try to dig up some notes on it to help out the Excel 2003 users, but sorry to say this is strictly an Excel 2007 scenario and exporting out to Excel 2003 won't work with the exact same steps I wrote. I published this because it took 3 MS tech support staff and 3 tries of different "select this, install that" combinations before we found what worked to export to Excel 2007. If you don't have Excel 2007 on the workstation you are developing on with BIDS you will need to install the drivers. Yes BIDS does not run in 64 bit - hence the need to toggle the SSIS package between T/F for the Run64BitRuntime. You can use the cmdline DTEXEC to run in an agent job the SSIS package in 32 bit mode if you want and leave the package in 32 bit mode. Sorry if my terms of "32 bit file" may not be technically correct - I was an instructor for years and try to always put things in plain terms that newbie to advanced can understand. I won't get into the technical debate on it either as it seems to be covered by the other posters very well (Thank you!) If you are looking though for the how to make the Accountants happy with exporting data from the 64 bit SQL server to their standard Office 2007 Excel install with an SSIS package, this should hopefully guide you through the setup.
December 6, 2012 at 12:29 am
In my opinion a better solution is to create a linked server to the spreadsheet.
For this to work you will need the connectivity package deployed to the SQL server running the final SSIS package. Also the spreadsheet has to be placed on a fileshare mapped to a local drive letter with permission for the executing user to access and also the sheet name has to be static.
This will make you able to use a regular OLEDB Input data source connecting to the kocal server and then using the 4 part naming convention with linked server name first.
Then the package can by run on either a 32- or 64 bit machine with no special handling so migration between such instances is a breeze.
December 6, 2012 at 7:57 am
Great article. Wish I had seen something like this a few weeks ago - we spent nearly 2 days trying to get this working!
In my opinion a better solution is to create a linked server to the spreadsheet.
Using a linked server to the spreadsheet will not work very well when you are trying to create a new file each month/week/day.
December 6, 2012 at 8:03 am
gkolson (12/5/2012)
Thank you all for the good discussion and the positive comments - my first attempt at technical writing published at a larger scale. Hopefully now to some answers: When I used to have an export to Excel 2003 I had to install a completely different set of Access database engine files on the server for the Excel 2003 SSIS. I will try to dig up some notes on it to help out the Excel 2003 users, but sorry to say this is strictly an Excel 2007 scenario and exporting out to Excel 2003 won't work with the exact same steps I wrote. I published this because it took 3 MS tech support staff and 3 tries of different "select this, install that" combinations before we found what worked to export to Excel 2007. If you don't have Excel 2007 on the workstation you are developing on with BIDS you will need to install the drivers. Yes BIDS does not run in 64 bit - hence the need to toggle the SSIS package between T/F for the Run64BitRuntime. You can use the cmdline DTEXEC to run in an agent job the SSIS package in 32 bit mode if you want and leave the package in 32 bit mode. Sorry if my terms of "32 bit file" may not be technically correct - I was an instructor for years and try to always put things in plain terms that newbie to advanced can understand. I won't get into the technical debate on it either as it seems to be covered by the other posters very well (Thank you!) If you are looking though for the how to make the Accountants happy with exporting data from the 64 bit SQL server to their standard Office 2007 Excel install with an SSIS package, this should hopefully guide you through the setup.
Well done on your first attempt.
Just to clarify Run64BitRuntime does not make any difference to running it via anything else then in BIDS. So if you are developing and you need it as true, leave it as true. There is no need to "toggle" as it does not make any difference when deployed to a server and run.
Again... well done! Braver then i 🙂
December 6, 2012 at 12:09 pm
peter.mclean (12/6/2012)
Great article. Wish I had seen something like this a few weeks ago - we spent nearly 2 days trying to get this working!In my opinion a better solution is to create a linked server to the spreadsheet.
Using a linked server to the spreadsheet will not work very well when you are trying to create a new file each month/week/day.
That obstacle could be overcome by a simple File Copy Task with the destination being a variable evaluated as an expression.
December 6, 2012 at 1:43 pm
A word of warning. We had this requirement at the office recently and the production support team noticed that the driver is only supported "provided the SSIS jobs run in the context of a logged-on user with a valid HKEY_CURRENT_USER registry hive". i.e. for a typical scenario where SSIS packages are scheduled to run on a server under a system account it is not supported.
In the end we used OpenXML and C# code in a script task.
December 7, 2012 at 12:53 am
seanoregan (12/6/2012)
... i.e. for a typical scenario where SSIS packages are scheduled to run on a server under a system account it is not supported.
Is that a typical scenario? As far as I know, I thought best practices dictate to use a proxy account (which is just a regular domain account) which has just the necessary permissions to run the job.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 8, 2012 at 5:39 am
gloing59 (12/8/2012)
very good website:===== http://www.globalmarket.name// =====
The website wholesale for many kinds of fashion shoes, like the nike, jordan, prada, also including the jeans, shirts, bags, hat and the decorations.
WE ACCEPT CREDIT CARD /WESTERN UNION PAYMENT
YOU MUST NOT MISS IT!!!:-D:-D:-D:-D
Reported as spam. Dumb spam at that - can't even create hyperlinks properly.
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
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply