Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2007
    Posts
    31

    Question Unanswered: Failure Workflow Does Not Fire

    Hello,
    I have a SQL Server 2000 DTS package in which the first step executes a batch file. The batch file contains FTP commands that log into an FTP server, and pull down whatever file is there.

    I set up a failure workflow to send an email if the step fails. When I have a SQL Server job run this package, and there is no file to dowload, the whole package fails without the failure workflow result firing.

    For the step (DTSStep_DTSCreateProcessTask_1), I have the 'FailPackageOnError' property set to -1. In the package properties, I have the check box for 'Fail Package on First Error' cleared.

    What do I need to do so that the failure workflow occurs when the step fails?

    Thank you for your help!

    cdun2

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Are you sure the fact that no file is there will cause the step to error out?
    Inspiration Through Fermentation

  3. #3
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    u need to trap errors from external programs like bat files to have the failed workflow activated. aslo not all external programs returns error code to the calling application. for bat files, u will have to set errorlevel to make the calling program understand the success/failure

    for example when using xp_cmdshell, this will call the failure workflow, if present
    Code:
    declare @err int
    exec @err = master..xp_cmdshell 'C:\xx.bat'
    if @err = 1
    RAISERROR ('err',16,1)
    the above code will not fire the failure workflow if u just execute
    exec master..xp_cmdshell 'C:\xx.bat'
    and even if the xx.bat is not present in folder C:\

  4. #4
    Join Date
    Apr 2007
    Posts
    31
    Thank you for your help!
    cdun2

  5. #5
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    Another option is to create an operator when you scheduled the job and an e-mail will be sent out if the job fails.

    Good luck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •