Oracle Instant Client and DTS data transformation from Oracle

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 o
n
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.
hjiWhen YOU ran it, it loaded the drivers off of YOUR machine. When you had
SQLAgent run it, it could find the drivers on the server. Make sure that
the SQL Server host machine also has the drivers loaded and under the
Windows User account you are running the SQL Server service accounts under.
I'm not sure how you are going to do this since you are running the service
account under Local System.
Sincerely,
Anthony Thomas
"hji" <hji@.discussions.microsoft.com> wrote in message
news:9F55BBC9-E1DF-4351-B9D9-4B92FDE74421@.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