Scheduled DTS Job fails

Question

Scheduled DTS Job fails

matey

SSCommitted

Points: 1845
More actions
August 30, 2006 at 4:42 am

#75046

Hi all
I dropped and recreated a DTS package and reschedule it as a job but only runs successfully as a DTS package, it fails with the following when executed as a job
Executed as user: Servername\sqlserver. ...art: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSExecutePackageTask_2 DTSRun OnProgress: DTSStep_DTSExecutePackageTask_2; DTSStep_DTSDataPumpTask_1: 0 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 0 DTSRun OnProgress: DTSStep_DTSExecutePackageTask_2; DTSStep_DTSDataPumpTask_2: 0 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 0 DTSRun OnProgress: DTSStep_DTSExecutePackageTask_2; DTSStep_DTSDataPumpTask_3: 0 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 0 DTSRun OnProgress: DTSStep_DTSExecutePackageTask_2; DTSStep_DTSDataPumpTask_4: 0 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 0 DTSRun OnProgress: DTSStep_DTSExecutePacka... Process Exit Code 1. The step failed.
Can someone help me out please because the job runs ok prior to my changing the Package and also runs successfully as DTS package after the change. Thanks

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

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

What does the package log say when the package is executed from the job?

If you previously specified a specific version of the package in the DTSRUN command in the job, you should change it to run just the package name so the latest version of the package will be executed.

Greg

John McC Hall of Fame Points: 3410 More actions · Answer 2

Does the specified user have sufficient privileges to execute the package? If you schedule the job to run with your ID does it run?

John

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 3

did you ever figure this out? I am having trouble with a scheduled DTS pakcage too. Everything seems to run fine from a command line and from the SQL Agent job, but it reports Exit Code 1 failure. ???? (with no errors) as a matter fact, I can test the return code at the command line by executing "echo %errorlevel%" and it says 1 too, but I'm not sure why.

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 4

funky. I figured it out.

The non-Zero Exit Code was due to my programmatically manipulating Step properties. I had set the ExecStatus of a step/task to "Complete" so that another step/task that depended on its completion would be able to run. I discovered this was the problem when I looked at the package [error]log and noticed that although the package was reporting a Successful completion, the step in question was reporting a failure at 12:00. I moved the dependency from the optional task to a mandatory task, and added a little Active-X to the workflow properties to check for the previous dependency to be inactive or complete.