Greetings, I have a stored procedure with a TRY / CATCH block. In the catch block I capture information about the error. And then use RAISERROR to "rethrow" the exception so that it will be available to SSIS. I notice that there is a defect reported which is about Execute SQL Task. "Execute SQL Task not signalling failure on a Raiserror." https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=276828&wa=wsignin1.0 It was fixed in SSIS 2008. But I found that RAISERROR still cannot fail a data flow source task. The stored procedure is executed in OLE DB Source task. The RAISERROR is justed omitted by OLE DB Source task and the task box turns green. But if there is any exception throw by system (eg. 1/0), it would fail the task. Here are the two stored procedures I tried. CREATE PROCEDURE [dbo].[GenerateErrorA] AS BEGIN BEGIN TRY Select 1/0 AS A END TRY BEGIN CATCH RAISERROR('Error Raised', 16, 1) END CATCH END CREATE PROCEDURE [dbo].[GenerateErrorB] AS BEGIN SET NOCOUNT ON; Select 1/0 AS A END Only stored procedure B could failed OLE DB Source Task. I would like to handle an error within my stored procedure without destroying SSIS's ability to detect a task failure. Is this possible? Is there an alternative to using RAISERROR? Thanks,

As mentioned in the Connect item for this, SSIS 2008 will have a fix for this. However, if you are working with SSIS 2005, you'd need to adopt one of the work-arounds mentioned. If your intention is to capture any exceptions in your stored proc for logging purposes, you can write the error details in a custom Log table from within the stored proc code itself, rather than letting the erro bubble-up to SSIS. And to fail the Source component in such a scenarios, siply return an exception like you are doing in your proc B. Hope this helps. Cheers! Muqadder.

I am using SSIS 2008 and wondering if SSIS 2008 fix it or not. RaiseError is able to fail "Execute SQL Task" but cannot fail "OLE DB Source". I don't know if it is my own problem.

Hi supertlj, I can reproduce the issue you mentioned. This seems to be a product issue. You could submit a feedback at https://connect.microsoft.com/sqlserver. Thanks, Jin ChenJin Chen - MSFT

A quick and pain answer...