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.
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.
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.
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.