July 8, 2005 at 5:43 am
I copied a DTS package from 1 server to another. This package was scheduled to run as a job on the old server. It is password protected.
On the new server I did a "disconnected edit" to change the connections... I am directly connected to the new server and can run the DTS package without any problems, so it's using the proper resources.
I scheduled the DTS to run as a job and it prompts me for the password, which I provided. When the job executes, it starts and shows that it is executing STEP1 Run DTS, but this job doesn't progress. The job normally takes about 10 minutes to run, but I let it run for about 2 hours. It's obviously haning on the DTS, but WHY?
I am sending the output of the job to a log file, but the log file is empty. I added a job step before the RUN DTS that just does a "select getdate()" and this step writes to the log file, so I'm pretty sure the DTS is not actually running, or it's immediately hanging.
Any suggestions are appreciated. Thanks
July 8, 2005 at 5:49 am
2 more things:
1. other DTS jobs are running when scheduled as job on this server
2. when troubleshooting, I did save a copy of the DTS without a password thinking this might be the problem
July 8, 2005 at 6:20 am
Could this be a security issue? If it runs when you execute it manually, yet not when it is executed by the SQL Server Agent, that is what I would suspect first. What does step 1 try to do - does it involve any network access?
Regards
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 8, 2005 at 6:35 am
The job does execute, but hangs at the RUN DTS step.
(during troubleshooting I added a new step to do a "select getdate()" and the Run DTS was Step2
Currently Step 1 of the job is RUN DTS...
Step 1 of the DTS is a transformation task importing data from Oracle to Sql Server.
The oralce connection is "microsoft odbc for oracle". I thought it might be security, but the connection is the same as it was on the old server; an oracle user/pw. Since I am logged directly into the server I'm using the server contect and manually the DTS does run.
July 8, 2005 at 10:24 am
Can you connect to the SQL Server using the SQL Server Agent login and run the package manually?
Greg
Greg
July 8, 2005 at 10:37 am
yes, I can connect and run the package manually.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply