How to force package to fail?

Last Post 24 Aug 2007 11:39 PM by SQLUSA. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
kbailey
New Member
New Member

--
23 Aug 2007 10:39 AM
I have a For Loop that goes to an ftp site and imports flat file(s) into SQL Server staging table. Sometimes the ftp file is not there. We call the SSIS pkg from within a job, and then process the staging table in a separate job step. I want to send an email when the ftp file doesn't exist, and then force the package to fail so that the SQL job also reports failure. I don't see a task to accomplish this. If I set FailPackageOnFailure to True on either the ftp task or the For Loop, the Send Mail task does not get processed. I'd like to just add a precedence on completion of the email path to just fail the package.

Second issue - Within the For Loop on the ftp Receive task, off of the ftp task I have Success path to other functionality, and Failure path to a Send Mail task. This Send Mail task never gets executed when the ftp task fails. I had to add a Failure path to the For Loop out to a Send Mail task to get the email to send. StopOnOperationFailure on the ftp task seems to make no difference - it appears to just not want to follow the Failure path. Any ideas why not?

Thanks,
Kevin
kbailey
New Member
New Member

--
23 Aug 2007 01:06 PM
The answer to the Send Mail problem was that there are three precedence constraints all ending on the Send Mail task, and they were set to LogicalAnd. That meant all three tasks had to fail in order for the Send Mail task to fire. Changing it to LogicalOr fixed this.

This makes no sense in this particular SSIS pkg, because the three tasks are all connected with Success constraints, so it is not possible for all three to fail. But I guess the designer can't determine that.

Still wondering if there's a component to force package failure... I suppose you just add a SQL task that automatically fails or something like that.
SQLUSA
New Member
New Member

--
24 Aug 2007 11:39 PM
There is no FailPackageOnSuccess flag available. You can put in a fail step following the email.

Also, there is a task which loads multiple ftp flatfiles, so you don't have to loop. It may or may not fit your application.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005grandslam/ - The Best SQL Server Training in the World
You are not authorized to post a reply.

Acceptable Use Policy