SQLTeam.com | Weblogs | Forums

Force Fail SQL task in SSIS

sql2005

#1

Hopefully that title makes sense.

I currently have an SQL task that copies data from a staging table to the final destination and creates a log entry in another table on success or failure.

I had to add an if statement to the task to have it check to make sure it matches a value in another table of the db. The SQL task of course evaluates to true and the incorrect status is logged.

How can I get the task to fail if the if statement is not met? I tried using RAISERROR() but that does not work.

Any suggestions?

Thanks,
Scott


#2

Disregard - I was able to get Raiserror() to work.


#3

Here is a small sample. You'll have to play with the severity of the error to make sure the event is passed on to the SSIS engine to interrupt the process.

IF (SELECT GETDATE()) = getdate()
begin
RAISERROR (13000,
               18, -- Severity.
               1 -- State.
               );

end