Dts package hanging when run in scheduled SQL agent job

Question

Dts package hanging when run in scheduled SQL agent job

Matte

Newbie

Points: 6
More actions
February 22, 2006 at 3:07 am

#68289

I have a scheduled agent job running a DTS package that collects data from an Oracle server.
Everytime I start the job manually or it is started by the schedule the job step "hangs"
The step is run as an Operating System Command
DTSRun /S PNOBPM01 /E /N "AraWin Transformation_DVH_SALG_FULL"
Running the DTS package manually in Enterprise manager works perfectly.
Any suggestions?

// Mathias Dalevi

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

mike gercevich SSCrazy Points: 2961 More actions · Answer 1

Check your permissions of the account that it is executing under during the non-interactive executions. In Ent. Mgr. it is executing under your personal login. During the other ones it is executing under System or some other account like Network Services.

-Mike Gercevich

Michael E. Baker Say Hey Kid Points: 676 More actions · Answer 2

Here's one I know all too well. Make sure if you have any ActiveX scripts, that all MSGBOX and INPUTBOX statements have been commented out.

Matte Newbie Points: 6 More actions · Answer 3

Thanks for your help but, I have not been able to solve the problem yet. The DTS package used is very simple, truncate an SQL table, select data from an Oracle server and then store it in the SQL table.

I have four of these packages, all of them having the same problem. The difference between them is the number of records to be retrieved.

I have turned on Package logging and can see that the job actually starts and in one case even finishes, but the job step does not complete in the scheduled job.

I also added a log file to the job steps and on one package that should retrieve around 500000 records I can see logg info stating that the job has started and collected aroun 210000 records and then nothing more is added to the log file.

On one other package only around 100 records should be fretrieved and here I get nothing in the log.

So there are no active X scripts connected with any message boxes etc.

//Matte

Antares686 SSC Guru Points: 125444 More actions · Answer 4

1) Connect to the server and run the DTS package manually in the workflow desginer to see if this produces the same issue.

2) Do not schedule the packages so no two are running at the same moment. We usually do 1 job with these back to back when hitting the same connection.

3) Upgrade your Oracle client, we had this same kind of issue with a v7 and a v8 client at one point but there was an update that fixed it.

drmccue Say Hey Kid Points: 700 More actions · Answer 5

Matte,

I recently had the same problem with a script similar to yours: Oracle OLE DB connection, Transform Data Pump task, to SQL Server. It ran fine when I ran it manually, in Enterprise Manager, but failed as a scheduled job. It turned out that the login account for the SQLSERVERAGENT service had been changed from Local System Account to another account on the domain. When I switched it back to Local System Account, the packages ran successfully.

This is what Mike Gercevich (above) was suggesting you check -- what is the login account for the SQLSERVERAGENT service on your server?

Hope this helps,

Dan McCue