Re: Oracle Instant Client and DTS data transformation from Oracle

From: hji (hji_at_discussions.microsoft.com)
Date: 01/04/05 

Date: Tue, 4 Jan 2005 15:05:05 -0800

Thanks Allan.

A) Yes, Instant Client is on the server;
B) Yes, it is running under SQL Server Agent service account. In this case,
it is local system account.

Thanks.

"Allan Mitchell" wrote:

> 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
>
>
>

Relevant Pages