Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

excel migration issue Expand / Collapse
Author
Message
Posted Wednesday, May 8, 2013 5:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 17, 2013 9:13 PM
Points: 21, Visits: 69
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.
Post #1450505
Posted Wednesday, May 8, 2013 5:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 12,877, Visits: 31,791
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1450506
Posted Wednesday, May 8, 2013 5:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 17, 2013 9:13 PM
Points: 21, Visits: 69
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.
Post #1450511
Posted Wednesday, May 8, 2013 5:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 12,877, Visits: 31,791
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1450515
Posted Wednesday, May 8, 2013 6:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 17, 2013 9:13 PM
Points: 21, Visits: 69
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.
Post #1450516
Posted Wednesday, May 8, 2013 6:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 12,877, Visits: 31,791
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1450518
Posted Wednesday, May 8, 2013 6:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 17, 2013 9:13 PM
Points: 21, Visits: 69
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?
Post #1450522
Posted Wednesday, May 8, 2013 7:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 12,877, Visits: 31,791
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1450540
Posted Wednesday, May 8, 2013 9:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 17, 2013 9:13 PM
Points: 21, Visits: 69
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.
Post #1450664
Posted Wednesday, May 8, 2013 10:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 12,877, Visits: 31,791
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1450665
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse