March 3, 2009 at 3:03 pm
Ok, I am thoroughly confused. I have created a simple dtsx package that writes a database table to a flat file. When I manually run the dtsx package it works fine and writes a delimited text file to the server. Problem is, when I schedule the job to run that package or manually start that job, it fails. It gives no reason for the failure, just that the job failed. This package does nothing else other than write that file. This seems so simple and straight forward. Yet no matter what I do it will not work.
Can anyone offer any suggestions ???
Jay
sql server 2000
sql server 2005
March 3, 2009 at 3:11 pm
I'll guess that the account that SQL Agent runs under doesn't have permission write to the flat file location. When you run the package yourself, any connection in the package that uses Windows authentication is using your account. When the package is run in a job, the SQL Agent account is used.
Greg
March 3, 2009 at 3:15 pm
How do I go about changing that in the job ? The job agent only allows the SQL Agent Service Account in the drop down......
Jay
sql server 2000
sql server 2005
March 3, 2009 at 3:18 pm
I agree with what Greg wrote. I recommend that you’ll read the fallowing article which gives a very good explanation about scheduling DTS packages and the problems - http://support.microsoft.com/kb/269074
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 3, 2009 at 3:24 pm
Ok, so the dtsx package shows me as the owner. The job shows me as the owner and I am an adminstrator on the box. The Server Agent is running as me as well. My account is a windows account. It looks to me like my ducks are all in a row, but I am definately missing something.
BTW, the path that the file is supposed to write to is hard coded in the connection for the flat file task and I have full control over the directory its going to (read/write).
Jay
sql server 2000
sql server 2005
March 3, 2009 at 3:58 pm
If SQL Server Agent is running as you, it throws out my idea. Maybe you should configure logging in the package so you can get some clue about why it fails.
Greg
March 3, 2009 at 6:45 pm
I tried that. Logging is turned on and set to write to a text file. Logging will occur when I run the package through SSIS, but when I execute the job it will not log.
Jay
sql server 2000
sql server 2005
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply