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

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 (AT) discussions (DOT) microsoft.com> wrote

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:

Who owns the job though?

Remember as well that LocalSystem account cannot see past itself onto the network

--

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 (AT) discussions (DOT) microsoft.com> wrote

sa ownes the job. And this job does not need network access in order to run.

Just for the heck of it, I changed the owner to my domain login account,
which is admin and sysadmin and rescheduled the job so SQL Server Agent would
invoke the job. It failed once again.

I then tried something different. I thought maybe I could add a Linked
Server to Oracle using Instant Client. I went to Enterprise Manager and used
the UI to add this. The strange thing is that Oracle Instant Client driver is
not listed in the available data provider drop-down list, which is consistent
with the error message that I got earlier "Specified driver could not be
loaded due to system error 1114". Recall that Instant Client is available
both in the ODBC applet and DTS designer though.

Any additional ideas?

Thanks a lot.
hji

"Allan Mitchell" wrote: