September 12, 2007 at 2:28 pm
I have serveral scheduled jobs that run of a morning. Since the upgrade from SQL2000 to SQL2005, some jobs fail with an error similar to the one listed below. These jobs generally call DTS packages that really do nothing more then move data between tables. These jobs fail consistenly in the morning when all the jobs for the server run (which is staggered over about a 4 hour time period). What puzzles me is the DTS packages that these jobs execute, run just fine when executed manually. I am stuck because I don't know if I need to fix my DTS package syntax / logic or if I'm encountering more of a performance / timing problem. Any ideas are greatly appreaciated.
Example error as seen in job history:
Executed as user: AD\PremiseKCTPS. ...Start: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 2000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 2000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 3000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 3000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 4000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 5000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 5000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 6000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 6000 ... Process Exit Code 1. The step failed.
September 13, 2007 at 9:21 am
You can't really get any info about why a package failed from the job history. You should enable package logging, execute the job, then check the package log to see the reason for the failure.
I assume you're running the legacy DTS packages and haven't converted them to SSIS packages. I've recently found that there isn't a way to view the package logs in SSMS like there is in Enterprise Manager, so you'll either need to query sysdtspackagelog and sysdtssteplog in msdb or set the package to log to a file.
Greg
Greg
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply