DTS job failing

Question

DTS job failing

Neeta Sharma

SSC Journeyman

Points: 96
More actions
September 6, 2005 at 11:37 pm

#89755

Hi All,
I have a problem. Hope someone can help.
I have a dts which runs fine when i execute it manually. But when i schedule it, the job fails. In the job history, the error I am getting is :
Executed as user: HO-Server\admin. DTSRun: Loading... Error: -2147467259 (80004005); Provider Error: 0 (0) Error string: Timeout expired Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.
One more thing, my dts is connecting to another server but there is no problem in the connection string so I dont think it can be an issue and also the dts runs fine when i execute it manually.
Thanx and Regards

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

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

philcart SSC-Forever Points: 47795 More actions · Answer 1

Error: -2147467259 (80004005);

This is the generic "Access Denied" message. So there is a permissions problem somewhere along the line. What account(s) are the SQL Server services (both Server and Agent) using?

Also, turn on Package Logging via the Package Properties page. The you'll be able to see a bit more detail than just the job history.

--------------------
Colt 45 - the original point and click interface

Neeta Sharma SSC Journeyman Points: 96 More actions · Answer 2

Hi Phil,

Thanx for your reply.

I dont think there is any permissions problem coz the login we are using is Admin login and also the dts is running fine when run manually. If there were a permissions problems then the dts would have failed manually also.

I enabled the Package logging also but there is no error log coming. When I ran the job manually, at that time the package ran successfully so there was a log but of successful run of package. I dont think the package logs containg error log for scheduled jobs also.

Thanx and Regards

philcart SSC-Forever Points: 47795 More actions · Answer 3

As I said in my previous post Error: -2147467259 (80004005); means access denied. So yes there is a permissions problem.

When you execute the DTS manually it runs under your security context. When the DTS is run from the schedule it is run in the security context of the SQL Agent, or the SQL Agent Proxy account. Check the section headed "Package Scheduling and Security Issues" on this page, http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_pkgmng_sec_09dl.asp

When you enabled Package Logging, did you select to log to SQL Server, or to a file. I recommend logging to SQL Server because it's easier to view the logs. For logs written to a file, the same file is used and log information is appended.

Once enabled, DTS will always create a log every time the package is executed. To view the log in Enterprise Manager, right-click on the package and choose "Package Logs..." this will give you a dialog where you can view the logs that have been created. Then you can open the log and see the detail for each step in the package.

--------------------
Colt 45 - the original point and click interface

RoelofK68 SSC-Addicted Points: 456 More actions · Answer 4

I had the same problem before as well. I am not sure what your DTS does, but if for e.g. it exports to a file, the server will need to have access to where you save the file to.

For me it helped to have the server logged in and locked as a user that has rights to the directory or share the file is saved to.

<hr noshade size='1' width='250' color='#BBC8E5'>Kindest Regards,

Roelof
<a href='http://' class='authorlink' target='_blank'></a>

--There are only 10 types of people in the world. Those who understand binary, and those who don't.