DTS job reports "Successful" but it is not 1

[drStealth]

This is puzzling to me....we are on SQL 2000 SP 4, Windows server 2003. We have several DTS processes scheduled as jobs. When SQL Agent executes the job it reports as being "Successful". However, the file has not updated the data with the export as it should.

The job is run using a domain account with what appears to have all required permissions. This is the same account that SQL Agent uses.

I've also tried setting up the job as a T-SQL command (rather than CmdExec) which executes the xp_cmdshell stored proc, with the same result.

There are no problems executing the DTS manually. I once set up the job with the same owner as the account on the local workstation, but got the same results.

Any input from the SQL DTS gurus would be appreciated. Thanks. drStealth

 

[mrdenny]

If an error condition is not being thrown within DTS then the job will not report a failure.

For the file path in the DTS package to the file are you using the full UNC path to the file, or a local path?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[drStealth]

Denny, thanks for your post...

I am using the full UNC path.

I believe it is some sort of permissions issue, but if the account is already a domain account with proper privileges, then what am I missing?
drStealth

 

[mrdenny]

A permissions error should return an error to the DTS package which should then return an error to the SQL Server.

Try running the DTS package from the servers console and see what it does (instead of from your workstation).

You can also turn on logging in the DTS package. Right click the package, properties, logging tab. Enter the server name in the SQL Server name field and click ok.

Then after the package has run, right click on the package and select View Logs. From the tree select the most recent version, and the most recent run time and click view logs. This will show you each step in the DTS package and what step is any failed as well as the error it received.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com