Re: Oracle Instant Client and DTS data transformation from Oracle

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 01/04/05

  • Next message: DavidM: "Re: Sample DTS Packages?" 
    Date: Tue, 4 Jan 2005 21:33:07 -0000

    Remember DTS is client side so it will

    A) Execute on the server so the drivers need to be there
    AND
    B) Execute in the context of either the SQL Server Agent service account or the proxy

    http://support.microsoft.com/?kbid=269074 

    -- 
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"hji" <hji@discussions.microsoft.com> wrote in message news:ACEA89AE-EAB8-4529-B622-CDEDAEE9AD0F@microsoft.com...
> I've tried to use Oracle 10g Instant Client driver so I can transport data
> from Oracle to SQL Server without success so far. Anybody can enlighten me on
> this subject? Thanks in advance.
>
> 1. I downloaded and setup Instant Client according to Oracle documentations.
> I modified PATH, added a few environment variables, and setup TNSNAME.ORA
> files;
> 2. I was able to see the Oracle Instant Client driver, both from ODBC
> applets within Control Panel and DTS designer's data source;
> 3. I created a DTS data transformation task and was able to run it. The data
> was transformed successfully;
> 4. I scheduled this package as a SQL Server Agent job. The job fails. This
> is the message I got:
> Executed as user: ServerName\SYSTEM. DTSRun:  Loading...   DTSRun:
> Executing...   DTSRun OnStart:  DTSStep_DTSDataPumpTask_1   DTSRun OnError:
> DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)      Error string:
> Specified driver could not be loaded due to system error  1114 (Oracle in
> InstantClient).      Error source:  Microsoft OLE DB Provider for ODBC
> Drivers      Help file:        Help context:  0      Error Detail Records:
>  Error:  -2147467259 (80004005); Provider Error:  160 (A0)      Error
> string:  Specified driver could not be loaded due to system error  1114
> (Oracle in InstantClient).      Error source:  Microsoft OLE DB Provider for
> ODBC Drivers      Help file:        Help context:  0      DTSRun OnFinish:
> DTSStep_DTSDataPumpTask_1   DTSRun:  Package execution complete.  Process
> Exit Code 1.  The step failed.
> 5. I scheduled this job as sysadmin, so I don't think access is the issue
>
> By the way, I did all this via Terminal Services on this server. Not sure if
> that has anything to do with the problem I had.
>
> Thanks.
>
> hji
  • Next message: DavidM: "Re: Sample DTS Packages?"

    Relevant Pages

    • Re: SBS 2003 - SBS 2008 migration issues
      ... DHCP cannot work properly with the hyper-v role, and probably DNS, neither will the configuration wizards. ... clients losing the ability to use it after removing trend client, reseting winsock / removing network components / reinstalling teh client don't bring it back. ... When I add the 32 bit driver to the printer it simply tells me it's not a suitable driver, and to try adding the driver from a 32 bit client. ... If I install hyperV on the SBS 2008 will it run a win2k3 server? ...
      (microsoft.public.windows.server.sbs)
    • Re: Printer autocreation failing
      ... behaviour of a TS incompatible printer driver. ... MCSE, CCEA, Microsoft MVP - Terminal Server ... Tried installing that printer tray hotfix, ... Also tried updating the RDP client to the latest ...
      (microsoft.public.windows.terminal_services)
    • Re: Printer autocreation failing
      ... reinstall the driver onto the TS box). ... Check in Printers and Faxes - File - Server properties - ... Also tried updating the RDP client to the ... box and installing the print queue from the remote server ...
      (microsoft.public.windows.terminal_services)
    • Re: Printer autocreation failing
      ... redirection of the driver to the LJ4) and functional. ... Check in Printers and Faxes - File - Server properties - ... Also tried updating the RDP client to the ... box and installing the print queue from the remote server ...
      (microsoft.public.windows.terminal_services)
    • Re: .webpnp Driver file
      ... I think the client user will need to be an admin user if the driver contains ... Server is able to create webpnp file. ...
      (microsoft.public.windows.server.clustering)