DTS Packages in Clusters

Question

DTS Packages in Clusters

Santhosh Kumar

Right there with Babe

Points: 766
More actions
August 15, 2005 at 11:33 am

#60163

HI,
We have a couple of DTS packages in a 2 node cluster. We have a problem like when the database is in its primary node then those dts jobs gets runs. But when the database is failed over then those dts jobs fails(When I run the package directly it runs but not as a job). The error I find is
Executed as user: PP\svcentsqlcl. The process could not be created for step 1 of job 0xF642B049F90FC045A1577BC55BFFEA5A (reason: The system cannot find the file specified). The step failed.
In the above PP\svcentsqlcl user is a domain user and service account under which the SQL Server runs in both the nodes. It has all rights.
I have even tried to recreate the job in the fail over node. It does not work either. Can you please let me know if you have any ideas on this. Except this DTS job all other jobs run fine.

With Smiles
Santhosh

Viewing 13 posts - 1 through 12 (of 12 total)

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

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 1

Are you referencing files that are not on the shared storage, say d:? in order for the DTS to work all of the resources need to be available to both nodes, also, are you connecting to the virtual cluster name or the name of the 1st node?

Just a couple of things for you to check, let me know if you need anything else and i'll see what I can sort out for you.

Jeff Gray SSChampion Points: 10667 More actions · Answer 2

I think I recall seeing this before. I believe that you might need to check the location that the job step's log file is pointing to. If the directory doesn't exist, the step won't run. It looks from the error message that the Agent isn't even trying to run the DTS package.

So, if you are logging to a local drive, say "c:", you'll need to make sure that the directories exist on all cluster nodes. Better yet, move the log files to shared storage.

hth JG

Jeff Gray SSChampion Points: 10667 More actions · Answer 3

Just a point of clarification: each step can have its own log file, so you need to check this for each job step.

JG

Santhosh Kumar Right there with Babe Points: 766 More actions · Answer 4

Jeff,

Thanks for your reply. I have saved the DTS package in side the SQL Server itself. I did not save the package externally. Can you let me know in which path the log files will be there? Since I verified the SQL Server error log and the event log, I could not see any errors there. While failing over all the dependent disks of the SQL Server is being failed over to the another node. Letme know if you need any clarification.

With Smiles

Santhosh

Jeff Gray SSChampion Points: 10667 More actions · Answer 5

When I recieved a similar error message, the problem was with the JOB configuration for the step that runs the DTS package.

In EM: expand the server, and then expand SQL Agent. Select the Jobs icon, and identify the job that won't run. Open up that job, and select the steps tab. Open the job step, and click the Advanced tab.

The log file for the step is located in the bottom half of the window. If the path specified in the "output file" text box does not exist, it will cause the step to fail. The agent will not create any subdirectories for this file, so the entire path must exist already.

There is also a log location specified in the logging tab of the DTS package properties, I'm not sure that this would create the same problem that you're having.

This may not be the cause of your problem, but I hope this is helpful.

JG

Santhosh Kumar Right there with Babe Points: 766 More actions · Answer 6

There is no error log location set up either in Job or in the DTS packages. Do you have any suggesitions?

Jeff Gray SSChampion Points: 10667 More actions · Answer 7

Does the DTS package access a file as either a data source or destination? If so, is that file on a UNC path?

I think if this were the case that the message would be different, but I'm running out of ideas.

Can you re-create the job step using an non-encrypted DTSRUN command? See the BOL for the syntax.

Peter Kryszak SSCarpal Tunnel Points: 4368 More actions · Answer 8

Can you run the DTS interactively on the backup server?

If you're sure that you've reviewed all file accesses within and around the DTS and job, then monitoring to catch unknown file access seems to be the only choice.

Check the system event logs for any failures.

You could also log file access to try to capture the failed access. I can't vouch for this utility in your situation but it might help: http://www.sysinternals.com/Utilities/Filemon.html. Sysinternals has an excellent reputation but running this on a production SQL server might not be risk-free.

Jeff Gray SSChampion Points: 10667 More actions · Answer 9

Peter,

That's where I was thinking of going next. Like most of the sysinternals tools, filemon is a stand-alone executable that doesn't require "installation". I've tested it and it has no ill effects in production, but that was after testing.

JG

Santhosh Kumar Right there with Babe Points: 766 More actions · Answer 10

Peter,

Yes I am able to run the DTS package from the design mode or the from the DTS designer. I even tried to re create the job in the fail over cluster. There is no error in the logs too.

Santhosh

Peter Kryszak SSCarpal Tunnel Points: 4368 More actions · Answer 11

That really indicates an access control issue.

Are you sure that the job in the failover machine is owned or run by the SQL Agent account? It is a domain account?

Make sure that the account is not locked due to password expired or too many wrong attempts.

Santhosh Kumar Right there with Babe Points: 766 More actions · Answer 12

Peter,

Yes its a domain account. If that is a control issue then all the jobs will fail. But only this particular DTS jobs are failing other backup and other maintenance jobs are runing fine. Today I just failed over that Database back to its original position and it has started working.

Santhosh