DTS oracle Jobs hang

hello:
My situation is
(1) I upgraded MS SQL 7.0 from original edition to SP 4
(2) DTS packages which download data from an oracle server work fine both on a remote machine and on the Server
(3) When these packages are scheduled to run as jobs they do not succeed and do not fail they just keep running e.g for 58 hours over the weekend
(4) even if I start the job on the server I still get the same behaviour:

Can anyone help please?I have lots of experience with running DTS packages to Oracle, but none in a SQL 7 SP4 environment. These are the things that I would do:

1. Check your application error log; look for any warnings and/or errors that are related to Oracle.
2. Open of SQL*Plus on the server; attempt to connect. Look for any error messages or warnings.
3. Delete and re-initialize the Oracle connection objects in your DTS packages; this can be a pain. Try creating the new connection first and then changing the connection name in the connection objects that are "in the flow".
4. Save the DTS package as a VB file and open (and execute) the package in VB. I dunno if this is an option with SQL 7.0 SP4; it is with 2000.

Sorry, that's not very much to go one for right now. I have seen this issue with AS/400 connections (frequently). It boiled down to an application pop-up window which was not visible when the DTS job ran as a job (but which was visible when executed from the DTS pacakge maintenance window).

Best of luck.

Regards,

hmscott|||Thank you HMScott

I've investigated these and still could'nt find the problem. Then I noticed that the jobs actually work but are not marked as finished. This seems identical to http://www.dbforums.com/t943042.html except that this SQL 7.0 and that was SQL 2000. I still cant get the jobs to mark themselves as finished. ??|||Out of curiousity, where are you seeing that the jobs are not marked "finished". If you are looking at the jobs through the job monitor (under Management/SQL Server Agent/Jobs), are you remembering to periodically refresh the screen (press F5)?

Regards,

hmscott|||I sure do HM (F5 that is) The jobs normally take a minute but when I cancelled them they had been running for 58 hours plus. I'm begining to wonder if it's something to do with the mail client. This machine was also hit by the Lovebug virus which is mail related I believe.

I'm no expert but I was wondering if there was anyway I could get a hint as to whats wrong. I looked in sqlagent.out but could see nothing.
I ran a trace and then started the job. Could'nt see anything obvious but to be honest was a bit lost in there in Trace World.|||Can take help of this KBA (http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074) to schedule dts as a job.

And also this Winnet mag article (http://www.winnetmag.com/Article/ArticleID/14399/Windows_14399.html) for reference.

HTH|||Thanks to all for their input:

Current situation
The jobs are now working.
I went into the Package properties and disabled the "USE OLE DB service components" and now the jobs work and mark themselves as completed. If I enable this then the Oracle jobs wont mark themselves as completed.
This weird as the connection still uses the "Oracle provider for OLE DB"

Nevertheless it is progress