April 16, 2004 at 7:28 am
Is there a way to set the DTS pkg execution status return code to "success" ? I have a pkg that runs all tasks/steps successfully (except for ones that are only executed based on an error in a previous step) but the execution status returned to the job says the DTS pkg failed. If I reach the last task/step in the DTS pkg I want to be able to set this success return code for the pkg to the job. Is this possible?
Jeff
April 16, 2004 at 7:51 am
Rather than try to eliminate the return of the package execution you should probably establish why the package is failing and fix that otherwise you leave yourself open to other problems down the road.
April 16, 2004 at 2:21 pm
I'm trying to fix the return code programmatically for the DTS pkg execution, not "eliminate" it. That's impossible. According to the pkg log, all the "executed" steps are successful. The only steps which don't execute are ones which have on error precedence. There are no pkg errors, and this pkg has been run hundreds of times in the last 15 months. I don't see how this is different from setting the return code within a stored procedure to communicate success/failure. I just want the job that runs the pkg to show successful execution when the DTS pkg execution is successful (as there are no other steps in the job). It's possible to manipulate execution status of a DTS step to "skip" it. I'm asking how to do this for the entire pkg.
Jeff
April 16, 2004 at 4:20 pm
I agree with stacenic. Look for why your package is returning an error. If all of the steps that are supposed to run in the package are completing successfully and the package itself is still returning an error, you may want to check your workflow settings for the last task in the process. Perhaps you have the workflow set to on completion or on success and then moving to an On Failure Task. But, just for the record, If you just wanted to force the package to report success no matter what, I suppose you could add an ActiveX task as the last Task performed in the package. The default code executed is the Function Main is:
Main = DTSTaskExecResult_Success.
If this is the last thing to run it should make the package report a success. You may want to include some debug code in this task to ensure that it is indeed executing. Something like Msgbox "Task Executed" or something.
April 19, 2004 at 10:22 am
The "problem" is: How do you programmatically skip a task/step in DTS so that the execution status of the skipped step is reported as success and consequently the "process exit code" for the pkg execution is reported as zero (success) and not one (failure) to a job?
Table msdb.dbo.sysdtspackagelog has ErrorCode=0 (success) for the pkg, and table msdb.dbo.sysdtssteplog has ErrorCode=0 (success) and StepExecStatus=4 (success) for all steps in the pkg (including the skipped step). I'm setting the execution status of the step to script in a VBScript Active X task using the following statement:
oPackage.Steps("DTSStep_DTSExecuteSQLTask_3").ExecutionStatus = _
DTSStepExecStat_Completed
However, the error log file shows different info from the pkg log and step log! It shows the above skipped step as failed, and this is why the job thinks the pkg failed:
Step 'DTSStep_DTSExecuteSQLTask_3' failed
Step Execution Started: 12:00:00 AM
Step Execution Completed: 12:00:00 AM
Total Step Execution Time: 0 seconds
Progress count in Step: 0
I'm not sure where I get the above VBScript code to skip the step, but the following works better and reports the step's execution status as success to a job when it is skipped. (I tried setting ExecutionResult for the step but was not able to.)
oPackage.Steps("DTSStep_DTSExecuteSQLTask_3").ExecutionStatus = _
DTSStepExecStat_Inactive
The error log for this step now correctly shows:
Step 'DTSStep_DTSExecuteSQLTask_3' was not executed
Thanks for leading me to the solution.
Jeff
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply