May 8, 2013 at 5:29 am
Hi. We run a STD2008 64 bit relational engine SP1 in production.
We recently migrated a pkg that reads an xlsx excel file from a separate server and got the following error.
Error: 2013-05-07 13:04:16.65
Code: 0xC00F9304
Source: xxxxxxxx Connection manager "Excel Connection Manager" where xxxxxxxx is the name of a sub package.
Description: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.
A long time ago when the project started we followed the instructions at this link to install a provider on the target production machine (where the pkg would run) http://www.microsoft.com/en-us/download/details.aspx?id=23734 instead of installing excel there. This was in anticipation of this pkg's needs. I believe the goal was to have a 2007 excel connector for ssis. I believe we forgot to test this plumbing when the project switched hands.
From our network guy's docs, it looks like an app called AccessDatabaseEngine is what got installed as a result and I'll be checking soon with him to learn if it somehow was uninstalled.
I checked the solution and noticed that properties-> debugging->run64bitRunTime has been set to true all along during testing on my 32 bit client machine where excel 2007 is installed. The pkg runs from a command line in production. I have to check if excel 2007 is installed on the server from where I get the excel file. That may be just a file server and I'm not sure if an excel installation (or connector installed there) there makes a difference to ssis or not. I'm suspecting no. That actually opening the excel file in an ssis pkg takes place on the ssis server.
I'll look up the error but am posting here in case the community knows where we went wrong and if we have the option of remaining 64 bit.
--more info
I see one workaround at http://sqltechconsulting.com/2010/03/29/error-0xc00f9304-the-excel-connection-manager-is-not-supported-in-the-64-bit-version-of-ssis/ but that worries me considering I want to take full advantage of a 64 bit version . It suggests that the command line point at location “D:Program Files (x86)Microsoft SQL Server100DTSBinndtexec.exe” /f “D:Test.dtsx” /X86 . I think the author meant . D:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn . I'll try to look up the meaning of the trailing character /X86 but if the community knows whether or not i need it, please let me know. Also, from other posts it appears the debugging 64 bit flag is only relevant in design mode. Also, it appears that the x86 directory does also exist on my target server. Currently my .bat file points at "C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec" which I understand is the 64 bit executable. Also, I read something somewhere that even if you run the 32 bit executable, being on a 64 bit machine can still pose problems. Any straight answers would help.
I do see at http://www.mssqltips.com/sqlservertip/1812/dtexec-command-line-parameters-using-command-files/ that the /x86 param is ignored when run from the command line.
May 8, 2013 at 5:35 am
there is a very similar issue when adding linked servers for Excel;
I tripped over this before , where Office 32 bit was installed on my server, and you get in a catch-22 where you cannot install the 64 bit ACE drivers, and even if you uninstall Office 32, some bits are left behind that prevent the 64 bit version of office, or the ACE drivers from being installed.
fortunately, there is a solution!
install the AccessDatabaseEngine_x64.exe from microsoft:
http://www.microsoft.com/en-us/download/details.aspx?id=13255
The trick is a simple command parameter: make sure you open an Administrative command prompt window, and run it with the c:\Downloads\AccessDatabaseEngine_x64.exe /passive
command line flag;
this will force the install of the drivers, even if you have 32 bit office installed;
otherwise you get some error about 32 bit Office preventing the install.
From there you should be good to go!
Lowell
May 8, 2013 at 5:49 am
thx. Will this step on what was probably a 32 bit engine he already installed? The reason I ask is that if we find this doesnt work, we can at least try running dtexec from the x86 32 bit directory and most likely invoke the original connector.
May 8, 2013 at 5:59 am
no it will not step on the existing drivers/installation, it forces a side by side install instead, so both the 32 bit and the 64 bit are installed and available for whatever processes you need;
i typically run 64 bit linked servers, but 32 bit ssis packages for example.
both run with no problems now that i have the 64 bit drivers installed as well.
Lowell
May 8, 2013 at 6:04 am
thx. How 'bout 64 bit ssis pkgs. Do they run with excel. I think you would know if you are running 64 bit ssis by seeing that the path to the dtexec program isnt the x86 path. BTW I hope linked servers arent germane to this answer. We dont use linked servers.
May 8, 2013 at 6:10 am
db042188 (5/8/2013)
thx. How 'bout 64 bit ssis pkgs. Do they run with excel. I think you would know if you are running 64 bit ssis by seeing that the path to the dtexec program isnt the x86 path. BTW I hope linked servers arent germane to this answer. We dont use linked servers.
not germane, no , they just suffer from the same underlying issue: both 64 bit SSIS and Linked servers both will fail unless the 64 bit ACE drivers are installed; it just getting the drivers installed has a bit of a hiccup in the process if the 32 bit ACE Drivers, or Office 2007 /Office 2010 32 bit are already installed on a server.
Lowell
May 8, 2013 at 6:25 am
hate to be a pain Lowell but are you running dtexec as 64 bit (from the non x86 directory) and bringing/reading excel 2007 data into your ssis buffer?
May 8, 2013 at 7:05 am
db042188 (5/8/2013)
hate to be a pain Lowell but are you running dtexec as 64 bit (from the non x86 directory) and bringing/reading excel 2007 data into your ssis buffer?
myself, specifically, no i do not use dtexec in 64 bit, regardless of directory names.
i only use interactive GUI of SSIS 32 bit when i'm doing imports via SSIS.
I often create linked servers in 64 bit for one-off excel imports.
Lowell
May 8, 2013 at 9:58 am
Lowell, this isnt specifically for you but I just learned from a peer that he got one of our test servers to run ssis in 64 bit mode with only (as far as he knows) a 32 bit access database connector available. Doesnt sound consistent with what I've been reading. We may try an experiment there with the known connectors available and then with them uninstalled. Seems strange.
May 8, 2013 at 10:04 am
i've read it's possible by doing something in the C:\Windows\SysWOW64\odbcad32.exe folder or something as well, but the method i'm mentioning is what i'm currently using;
glad you guys have a handle on it!
Lowell
May 8, 2013 at 10:07 am
i dont think we have a handle. We're likely not going to be able to explain why it runs on one server and not another. BTW the link you listed originally for download is for 2010. Do you believe it will be backward compatible to our 2007 excel files?
May 8, 2013 at 10:11 am
yes; the link i posted used the latest drivers that are compatible with opening all previous excel formats,
I've connected to pre-2007 xls, 2007 xlsx and 2010 xlsx spreadsheets with no problem before.
Lowell
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply