Oracle and SQL Server DTS

[LadyMarian]

We're having trouble scheduling a DTS package on SQL server to pull data from an Oracle 7.0 database. We are using the Oracle 7.25 drivers. The package runs fine in the interactive mode, but not as a scheduled job.

I posted this question to the SQL Server forum and tried what they recommended but it didn't work. Do you have any ideas that might help?

We called Micro$oft, but they are claiming it is all an Oracle problem.

Thanks in advance!

Here is the error we get when running the job:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147008507 (80074005) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error Detail Records: Error: -2147008507 (80074005); Provider Error: 0 (0) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error: -2147467259 (80004005); Provider Error: 160 (A0) Error string: Specified driver could not be loaded due to system error 126 (Oracle73 Ver 2.5). 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.

 

[hubi]

Check this

http://support.microsoft.com/support/kb/articles/Q264/0/12.ASP

 

[Guest_imported]

see PRB: DTS Transfer From SQL Server to Oracle Through OLE DB Provider Shows Ongoing Private Bytes Consumption (Q293320)


RESOLUTION
To work around the high consumption of private bytes, use the Microsoft ODBC Driver for Oracle or the Oracle ODBC Driver for Oracle. Neither of these drivers consume as much private bytes during large row transfers from Microsoft SQL Server to Oracle.

 

[Guest_imported]

I would like to do a migration from an Oracle 6 database residing in a digital microvax to a SqlServer 7 database resideing in a NT 4 server. I don't have the oracle 6 odbc drivers.... any help?

 

[MikeLacey]

Would it be feasible to extract the data as .csv files, define the tables in SQL/Server and then import the .csv files into the new SQL/Server tables? Mike
"Experience is the comb that Nature gives us after we are bald."

Is that a haiku?
I never could get the hang
of writing those things.

 

[suffolkss]

Does anyone have access to an Oracle73 ver 2.5 ODBC driver? I need it for an old spreadsheet which accesses an oracle db.

 

[Guest_imported]

I had this problem with SQL server and Progress.

It was solved by increasing the driver timeout.

 

[Guest_imported]

I am having the same problem as LadyMarian using SQLServer 7.0 and Oracle 8.1.6 ODBC drivers. I am trying to transfer data from SQLServer to an Oracle table. I get the same error message when running the DTS package through the scheduler, though the package executes without incident in the interactive mode.

I tried some of the suggestions and am still having issues. Was this ever resolved?

 

[Guest_imported]

Microsoft is full of $h!t, it's not an Oricle problem, it has to be a problem with their MDAC because I get the same error between SQL servers...

... DTSRun: Executing... DTSRun OnStart: Drop table [ecareiislogs].[dbo].[Object_Attributes] Step DTSRun OnFinish: Drop table [ecareiislogs].[dbo].[Object_Attributes] Step DTSRun OnStart: Create Table [ecareiislogs].[dbo].[Object_Attributes] Step DTSRun OnFinish: Create Table [ecareiislogs].[dbo].[Object_Attributes] Step DTSRun OnStart: Copy Data from Object_Attributes to [ecareiislogs].[dbo].[Object_Attributes] Step DTSRun OnError: Copy Data from Object_Attributes to [ecareiislogs].[dbo].[Object_Attributes] Step, Error = -2147008507 (80074005) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error Detail Records: Error: -2147008507 (80074005); Provider Error: 0 (0) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help co... Process Exit Code 1. The step failed.

 

[Guest_imported]

Did you ever get an answer to your DTS/Oracle problem? Because I've had this problem for a LONG time and yeap, both sides say it's the other's problem.

Blutzen@chw.org

 

[LadyMarian]

We eventually got it to work by having the job scheduled using the NT Scheduler. The login for the NT Scheduled task had to have rights to the Job in SQL.