I appreciate people's help in advance. Here is my issue:

I have created an Integration Services Package that contains an Execute
SQL task with the following code:

BEGIN
SELECT *
FROM dbo.TABLE
WHERE Period+Year IN (SELECT (Period+Year)SearchOn from TBL_OUTPUT
GROUP BY Period+Year)

IF @@ROWCOUNT = 0
RAISERROR ('MSG',21,1)

END

The purpose of the code is to check for a record in the "TABLE" table
before kicking off the extract to a flat file. I am running into an
issue. When I run the code in SQL Management Studio, it raises the
correct execption ("No Data Found"), however, when I run the same code
in the Execute SQL task, it does not fail the task, the task complete
sucessfully. I would like the Execute SQL task to fail if there are no
records in the table for the period and year combination.

I understand that the RAISERROR command is an issue in 2000, but I am
still having issues with 2005.

I look forward to people's thoughts.

Hello rcaruso (AT) hotmail (DOT) com,


So here is what I did

BEGIN

SELECT 1 as VALUE

RAISERROR('Bang',21,1) WITH LOG

END


The OnError Event Handler was correctly raised by the task.

SSIS package "Package.dtsx" starting.
Error: 0x0 at Execute SQL Task: Bang
Warning: 0x80019002 at Execute SQL Task: The Execution method succeeded,
but the number of errors raised (1) reached the maximum allowed (1); resulting
in failure. This occurs when the number of errors reaches the number specified
in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Task failed: Execute SQL Task
Warning: 0x80019002 at Package: The Execution method succeeded, but the number
of errors raised (1) reached the maximum allowed (1); resulting in failure.
This occurs when the number of errors reaches the number specified in MaximumErrorCount.
Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.


I have my MaximumErrorCount on the task set to 1.



Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com