SSIS Execute SQL Task and RaisError (Using a OLEDB Connection)
This is a relatively short post about raising an error in T-SQL and having SSIS not recognize that error. If you have an SSIS package that calls a SQL Task and that T-SQL for whatever reason ends up raising an error the SSIS package that called the T-SQL may complete with the step without error. There may be two separate reasons why this is not working as expected.
The first reason why it may not be working has to do with an apparent bug is SQL Server 2005 SP2. (It looks like it was a bug in 2008 as well) There are a number of good posts on this, but I believe you can fix this by installing SP 2 Updates. The first step is to make sure you know what version of SQL Server you are on. () The second step is to upgrade to a SQL Server build that has this error corrected. () I have chosen to installed the most recent Cumlative Update that was released on August 18th 2008, it will bring you to version 09.00.3282.00.
About the SP2 Error:
http://www.windows-tech.info/15/2c8831412be41b1f.php
The second reason has to do wiht the RaisError() call itself. The Error Level set in the RaisError statement needs to be set to a level of 11 or higher. That's it, it's that simple. Below is a simple example of testing this process.
Procedure:
Create proc [dbo].[failure_proc]
as
BEGIN
RaisError('Steves Error',18,1)
END
Unexpected Success (Error Level 11):
Expected Failure (Error Level 11):
Now I just need to integrate these changes into some of my previous packages and upgrade a few servers.