How do I fail a whole package depending on a return value

[PreacherUK]

a return value from an sp?

The sp checks that some values from tables equal each other. if the numbers equal then the results of an analysis package are correct. I then truncate a table. However if the numbers do not match to within a tolerance I want the truncate skipped (which I've done) but then I want the rest of the package that called the sp to fail or at least register as a fail as the sp is the last step in the package.

Any way of doing this?

 

[TheJon]

If you use RAISERROR within an Execute SQL Task you can pass control to the "on failure" path of a DTS package. I'm not sure what error value tips the balance, 1 does not, but 16 certainly does.

example:

[tt]if (condition)
begin
RAISERROR ('This is a test', 16, 1)
end
[/tt]

|_ |_  _  | _  _
|, | )(/, |(_)| )
          '

 

[PreacherUK]

sorry about this post, I had thought I had replied to an existing post :/

Thanks for the response, I'll look into this and update tomorrow.