Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    52

    Unanswered: DTS job hung !!! Please help

    I create a DTS package that copy data from oracle to SQL 2000. In the package, I have 2 connections, 1 transform data task, and 1 execute SQL task to truncate the table before copying the data. The DTS works by right clicking and EXECUTING.
    however, it does not work when I run it from a job. The job keeps saying
    "Executing...." and it runs for hours when it only takes a few seconds to run using DTS package

    DTS package owner is "sa"
    Job owner is "sa"

    What did I do wrong? Any suggestions are greatly appreciated.

    thank you

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Can't say just yet. Can you check on the following, just to see where the package is hanging?

    1) Which version of the package has been scheduled? If you are not sure, read up on the DTSRUN utility, and change the command that Enterprise Mangler automatically garbages up for you.

    2) Has the package connected to SQL Server? (Use sp_who).

    3) Has the SQL Server table been successfully truncated?

    4) Has the package connected to Oracle?

    Had the package just errored out after the truncate table, I would think that you are missing the Oracle SQLNET installation on the server. With a hang, you likely have a test textbox popping up waiting for someone to click OK.

  3. #3
    Join Date
    Feb 2004
    Posts
    52
    Thank you for your reply.

    The connection to SQL is working because the table got truncated. I am not sure how to test if the connection to Oracle is working. I did install ORacle client on the server and define ODBC data source to connect to the ORacle database. The DTS package runs if I right click on the package and select Execute.

    Any thing else I should check? Thanks

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Try this, then: Log on the server as the SQL Agent service account, and run the package from the command line. Use the one in the job, to be certain you get the right command. See if anything comes up.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •