Win 2003 x64 - SSIS error Importing xls and access data
I am having a problem importing data from xls and access into my SQL2005 DB usingSSIS.Would appreciate any help in getting this resolved.
Environment:
Xeon 64 bit processor machine/Win 2003 64 bit (x64)/SQL 2005 64 bit
Some of the resources that I have dug up so far have pointed to Jet Engine SP8 and WOW64.
A search on the box shows this:
C:\Windows\SysWOW64\Msjet40.dll – File version is 4.0.9025.0
Not sure what is missing.
The following is the error from the import from xls. The one from access is very similar.
==================================================================================
Task Import abc_xls
Validation has started
[DTS.Pipeline] Information: Validation phase is beginning.
Progress: Validating - 0 percent complete
[Excel Source [1]] Error:The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.
[DTS.Pipeline] Error:component "Excel Source" (1) failed validation and returned error code 0xC020801C.
Progress: Validating - 50 percent complete
[DTS.Pipeline] Error: One or more component failed validation.
Error: There were errors during task validation.
Validation is completed
[Connection manager "Excel Connection Manager"] Error:An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
[4073 byte] By [
sql_rv] at [2007-12-19]
There is only 1 DTexec.exe on this box. A search from c:\ on this box returns only this:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe
There apparently is no "Program Files (x86)" folder. btw, when we initially tried installing SQL Server, we were unable to install anything other than SQL2005 64 bit (since the OS was 64 bit). This means no SQL 2000, and no SQL2005(32 bit).If Jet is not available for 64 bit, it means we potentailly have to reinstall a 32 bit OS and start all over to have this working.
One of the workarounds (to avoid a complete rebuild) I can think of is importing the access/xls files to a 32 bit SQL on a different box and then DTS it over to this box.
Is there a better workaround that anyone has tried or can suggest? Thanks for all your responses. :)
Also, anyone has an idea if there is a roadmap for if/when Jet 64 for xls/access will be available?
 Summerstreet wrote: |
| OK! I found it. Thanks for the tip. Using NT scheduler works fine but if you want to keep all your sql jobs in one place then using the SQL Agent job scheduler is a better solution. However you do it you are still invoking a command line option. Please correct me if you think I'm wrong but I think that whether it is a dtsrun equivalent or executing a batch/script job we still need to execute in a 32 bit environment so shelling out to the command line for execution is our only option. |
|
You can easily run 32-bit DTEXEC.EXE from SQL Agent, just create 'Operating System (CmdExec)' step, provide path to 32-bit DTEXEC.EXE and command line arguments. You can use the same UI to help you build the command line agruments - create (temporary) SSIS step, provide all information, copy the command line arguments from Command Line tab page, paste them to the first (real) step, delete the temporary step.
