Search results matching tag 'Error Handling'

The client code that handles timeouts

Alexander Kuznetsov — Thu, 10 Jan 2013 21:05:00 GMT

After timeouts we need to make sure that active transactions, if any, are rolled back. All timeout handling must be done on the client. This post provides the implementation and unit tests.

Implementation

The following class extends SqlCommand and rolls back active transactions after timeouts:

public static class SqlCommandExtentions { public static void ExecuteNonQueryWithErrorHandling(this SqlCommand command) { try { command.ExecuteNonQuery(); } catch (SqlException e) { HandleSqlException(command, e); throw; } } public static SqlDataReader ExecuteReaderWithErrorHandling(this SqlCommand command) { try { return command.ExecuteReader(); } catch (SqlException e) { HandleSqlException(command, e); throw; } } private static void HandleSqlException(SqlCommand command, SqlException e) { const int timeoutCode = -2; if (e.Number != timeoutCode) return; var rollbackCommand = command.Connection.CreateCommand(); rollbackCommand.CommandText = "IF @@TRANCOUNT > 0 BEGIN ; ROLLBACK ; END ;"; rollbackCommand.CommandType = CommandType.Text; rollbackCommand.CommandTimeout = 0; rollbackCommand.ExecuteNonQueryWithErrorHandling(); } }

Testing

We need to unit test the two methods. For each one, we need to test the following four cases:

Command succeeds
Command blows up with some other error, which must not be caught by our handling
Command times out, active transaction
Command times out, no active transaction

The following tests cover these four cases for ExecuteReader. The other four tests are very similar, so there is no need to post them.

[Test] public void ExecuteReaderWithErrorHandling_WorksOnSuccess() { using (var dr = ExecuteReaderWithErrorHandling(sqlConn, "SELECT 1 AS n")) { Assert.IsTrue(dr.Read()); Assert.AreEqual(1, dr.GetInt32(0)); } } [Test] public void ExecuteReaderWithErrorHandling_HandlesTimeout_ActiveTransaction() { try { ExecuteReaderWithErrorHandling(sqlConn, "EXEC dbo.IWillTimeOut", timeoutInSeconds: 1); Assert.Fail("Must throw exception"); } catch(SqlException e) { Assert.AreEqual(-2, e.Number); VerifyNoOpenTransaction(sqlConn); } } [Test] public void ExecuteReaderWithErrorHandling_HandlesTimeout_NoActiveTransaction() { try { ExecuteReaderWithErrorHandling(sqlConn, "EXEC dbo.IWillTimeOutWithoutTransaction", timeoutInSeconds: 1); Assert.Fail("Must throw exception"); } catch (SqlException e) { Assert.AreEqual(-2, e.Number); VerifyNoOpenTransaction(sqlConn); } } [Test] public void ExecuteReaderWithErrorHandling_ThrowsOtherExceptions() { try { ExecuteReaderWithErrorHandling(sqlConn, "EXEC dbo.IThrowError8134"); Assert.Fail("Must throw exception"); } catch (SqlException e) { Assert.AreEqual(8134, e.Number); VerifyNoOpenTransaction(sqlConn); } }

These unit tests utilize a few database objects:

CREATE FUNCTION dbo.IWillCompleteInNSeconds ( @NumSeconds INT ) RETURNS INT AS BEGIN ; DECLARE @StartTime DATETIME , @Toggle INT ; SELECT @StartTime = GETDATE() , @Toggle = 1 ; WHILE DATEDIFF(SECOND, @StartTime, GETDATE()) < @NumSeconds BEGIN ; SET @Toggle = 1 - @Toggle ; END ; RETURN 0 ; END ; GO CREATE PROCEDURE dbo.IWillTimeOut AS BEGIN ; BEGIN TRANSACTION ; SELECT dbo.IWillCompleteInNSeconds( 5 ) AS SomeNumber ; COMMIT ; END ; GO CREATE PROCEDURE dbo.IWillTimeOutWithoutTransaction AS BEGIN ; WAITFOR DELAY '00:00:05' ; END ; GO CREATE PROCEDURE dbo.IThrowError8134 AS BEGIN ; SET XACT_ABORT ON ; DECLARE @i INT ; SELECT @i = 1/0 ; SELECT 1 AS n; END ;

After the timeout

Alexander Kuznetsov — Wed, 09 Jan 2013 18:23:00 GMT

After the timeout, we may sometimes, but not always, end up with an outstanding transaction. If this is the case, we need to rollback the transaction ourselves. Otherwise the consequent commands which use the same connection might not work as expected.

Let us debug through some client code and see what is happening in more detail.

Prerequisites

We shall need a few database objects, as follows:

CREATE TABLE dbo.Log1(InTran VARCHAR(20)); GO CREATE PROCEDURE dbo.TestTran AS BEGIN IF @@TRANCOUNT > 0 BEGIN INSERT dbo.Log1(InTran) VALUES('In Tran'); END ELSE BEGIN INSERT dbo.Log1(InTran) VALUES('Not In Tran'); END ; END ; GO CREATE FUNCTION dbo.IWillCompleteInNSeconds ( @NumSeconds INT ) RETURNS INT AS BEGIN ; DECLARE @StartTime DATETIME , @Toggle INT ; SELECT @StartTime = GETDATE() , @Toggle = 1 ; WHILE DATEDIFF(SECOND, @StartTime, GETDATE()) < @NumSeconds BEGIN ; SET @Toggle = 1 - @Toggle ; END ; RETURN 0 ; END ; GO CREATE PROCEDURE dbo.IWillTimeOut AS BEGIN ; BEGIN TRANSACTION ; SELECT dbo.IWillCompleteInNSeconds( 5 ) AS SomeNumber ; COMMIT ; END ; GO

Running the example

In the following test, add three breakpoints at the placeholder statements:

[Test] public void TimeoutTest() { for (var i = 0; i < 1000; i++) { using (var conn = new SqlConnection(localConnString)) { conn.Open(); try { const string iwilltimeout = "EXEC dbo.IWillTimeOut"; ExecuteSql(conn, iwilltimeout, 1); } catch (SqlException e) { System.Console.WriteLine(e.Message + " " + e.Number); } const string saveTrancount = "EXEC dbo.TestTran"; ExecuteSql(conn, saveTrancount); System.Console.WriteLine("Breakpoint placeholder 1"); } using (var conn = new SqlConnection(localConnString)) { conn.Open(); const string saveTrancount = "EXEC dbo.TestTran"; System.Console.WriteLine("Breakpoint placeholder 2"); ExecuteSql(conn, saveTrancount); System.Console.WriteLine("Breakpoint placeholder 3"); } } }

Let us start a profiling session that captures individual SP:StmtStarting and SP:StmtCompleted events. Also let us debug through this code.

First breakpoint - outstanding transaction

When we stop at the first breakpoint, let us run the following query:

SELECT * FROM dbo.Log1 WITH(NOLOCK)

It will return "In Tran", which means that after the timeout dbo.TestTran was called in the middle of an outstanding transaction.

Note: not all timeouts result in outstanding transactions. For example, later you can rerun this example using a different stored procedure:

ALTER PROCEDURE dbo.IWillTimeOut AS BEGIN ; BEGIN TRANSACTION ; WAITFOR DELAY '00:00:05' ; COMMIT ; END ; GO

You can see for yourself that when this procedure times out, there will be no outstanding transaction.

Second breakpoint - still outstanding transaction

Let us debug to the second breakpoint. Technically the first connection went out of scope and has been reused by the second using block, but we can still see the same uncommitted data:

SELECT * FROM dbo.Log1 WITH(NOLOCK)

The connection has not been reset yet - we do not see sp_reset_connection in the Profiler yet.

Third breakpoint - connection reset

Only when we actually get to run a command against the reused connection, the connection is actually reset. We can observe sp_reset_connection call in the Profiler, and the uncommitted data inserted by the first invocation of dbo.TestTran is gone.

Note: to my best knowledge, there is no guarantee that the same connection will be reused. It consistently happens on my box, but it could work differently on yours. As usual with closed source systems, one can never know for sure.

Consequences of continuing to use the same connection after the timeout

As we have seen, if we catch a timeout and later modify the database using the same connection, all the modifications done after the timeout may be eventually rolled back and lost, without us getting any error messages.

Conclusion

We need to rollback the outstanding transaction ourselves. We must also be aware that not all timeouts result in outstanding transactions. I have written two extension methods that implement it, and I will post it soon.

You may want to read more about connection pools here

Catching multiple exceptions on the client is robust and easy

Alexander Kuznetsov — Wed, 06 Jun 2012 17:30:00 GMT

Maria Zakourdaev has just demonstrated that if our T-SQL throws multiple exceptions,
ERROR_MESSAGE() in TRY..CATCH block will only expose one.

When we handle errors in C#, we have a very easy access to all errors.

The following procedure throws two exceptions:

CREATE PROCEDURE dbo.ThrowsTwoExceptions AS BEGIN ; RAISERROR('Error 1', 16, 1) ; RAISERROR('Error 2', 16, 1) ; END ; GO EXEC dbo.ThrowsTwoExceptions ;

Both exceptions are shown by SSMS:

Msg 50000, LEVEL 16, State 1, PROCEDURE ThrowsTwoExceptions, Line 4 Error 1 Msg 50000, LEVEL 16, State 1, PROCEDURE ThrowsTwoExceptions, Line 5 Error 2

The following C# code shows how easily we can access both exceptions:

try { var command = connection.CreateCommand(); command.CommandText = "dbo.ThrowsTwoExceptions"; command.CommandType = CommandType.StoredProcedure; command.ExecuteNonQuery(); } catch(SqlException e) { Console.WriteLine(e.ToString()); foreach (var error in e.Errors) { var exception = error as SqlError; if(exception != null) Console.WriteLine(string.Format("Number: {0} Message: {1}", exception.Number, exception.Message)); } }

Its output is as follows:

Number: 50000 Message: Error 1 Number: 50000 Message: Error 2

As we have seen, catching more than one exception is easy if we use C#.

SQL Server v.Next (Denali) : Exploring THROW

AaronBertrand — Mon, 22 Nov 2010 17:20:00 GMT

Sadly, THROW is not mentioned on the Programmability Enhancements (Database Engine) topic of Denali's "What's New" section. So, unless you were at PASS or have been reading the various blogs from the keynotes and other Denali sessions, the presence of this keyword may be news to you. I wanted to touch briefly on what THROW can do and, more importantly, what it can't do - before you start thinking about abandoning all usage of RAISERROR (or "RAISE ROAR" as Tobias calls it).

How THROW and RAISERROR differ

THROW outside of a CATCH block acts similar to RAISERROR, with a few notable exceptions:

The message_id parameter does not need to be defined in sys.messages:

THROW 66666, 'Hi', 1;

Result:

Msg 66666, Level 16, State 16, Line 1
Hi

With RAISERROR, you can use a string for the first parameter, but if you use a number that is not represented in sys.messages:

RAISERROR(54321, 16, 1);

Result:

Msg 18054, Level 16, State 1, Line 1
Error 54321, severity 16, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

Why should you care? Well, if you have applications that rely on certain error message numbers coming out of SQL Server (and ignore the text of the error message), converting to THROW will allow you to migrate to new servers without having to set up all of the messages in sys.messages.

The message_id parameter must be an INT (not a BIGINT) >= 50000. If you try to THROW a system message, you will get an error message instead of the error you wanted:

RAISERROR(14088, 16, 1, N'foo');
GO
THROW 14088, N'foo', 1;

Result:

Msg 14088, Level 16, State 1, Line 1
The table 'foo' must have a primary key to be published using the transaction-based method.
Msg 35100, Level 16, State 10, Line 1
Error number 14088 in the THROW statement is outside the valid range. Specify an error number in the valid range of 50000 to 2147483647.

There is no token substitution within the command itself, so printf formatting is ignored. Let's say we've added this message to sys.messages:
EXEC sys.sp_addmessage
    @msgnum = 66667,
    @severity = 16,
    @msgtext = N'There is already a %s named %s.';
With RAISERROR we can simply say:
RAISERROR(66667, 16, 1, N'foo', N'bar');
Result:

Msg 66667, Level 16, State 1, Line 1
There is already a foo named bar.

However, THROW does not accept any parameters for substitution. If you try:
THROW 66667, N'There is already a %s named %s.', 1, N'foo', N'bar';
Result:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

Since repeating the text defeats the purpose of saving the error message in sys.messages in the first place, you can get around this using FORMATMESSAGE():
DECLARE @msg NVARCHAR(2048) = FORMATMESSAGE(66667, N'foo', N'bar');
THROW 66667, @msg, 1;
Result:

Msg 66667, Level 16, State 1, Line 2
There is already a foo named bar.

Cumbersome, but it works.

The severity level for THROW is always 16 (unless it is a re-throw inside CATCH), and there is no way to force the breaking of the connection like we can with RAISERROR. Assume we have added this error message:

EXEC sys.sp_addmessage 
    @msgnum   = 66668,
    @severity = 24,
    @msgtext  = N'This is really bad.';

If we use RAISERROR, we can obey the severity level by re-specifying and using WITH LOG:

RAISERROR(66668, 24, 1) WITH LOG;

Result:

Msg 2745, Level 16, State 2, Line 1
Process ID 55 has raised user error 66668, severity 24. SQL Server is terminating this process.
Msg 2745, Level 16, State 2, Line 1
Process ID 55 has raised user error 66668, severity 24. SQL Server is terminating this process.
Msg 66668, Level 24, State 1, Line 1
This is really bad.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

There is no way to do that with THROW:

THROW 66668, N'This is really bad.', 1;

Result:

Msg 66668, Level 16, State 1, Line 1
This is really bad.

There is no THROW equivalent to WITH NOWAIT for immediate buffer output. With RAISERROR we can do this:
RAISERROR(N'Printing status...', 0, 1) WITH NOWAIT;
This can be quite useful in place of PRINT when monitoring a query with multiple steps, as it prevents waiting for the buffer to fill up before seeing real-time messages in the results pane of Management Studio.
Unlike RAISERROR, THROW honors XACT_ABORT (see Neils Berglund's post for more information).
There is no way to use WITH LOG in combination with THROW, or to "THROW" a non-severe error (e.g. one that prints in black in Management Studio instead of red, like the above example with severity level 0).

THROW inside CATCH

THROW inside a CATCH block acts like RETHROW - it will re-raise the exception that transferred to the CATCH block in the first place. An example:

BEGIN TRY
    RAISERROR(N'Hi from try.', 16, 1);
END TRY
BEGIN CATCH
    PRINT N'Hi from inside CATCH.';
    THROW;
END CATCH

This will allow you to perform other tasks, such as rollbacks, notifications, etc. before eventually throwing the same errors back to the caller - without having to understand and re-code all of the possible outcomes from within the TRY block. Note that THROW inside CATCH can re-throw multiple errors, not just the most recent one (which is all you have access to from ERROR_MESSAGE() and its cousins). And this is the only place where THROW by itself is valid syntax.

I am not going to pretend to be an expert in error handling, so I will leave it up to Erland to give you a great primer:

Error Handling in SQL 2005 and Later

A few more items to consider

Note that THROW() is one more case where the preceding statement needs to end with a proper statement terminator. So just as a gentle reminder, start using those semi-colons!

Also note that there is still a very noticeable omission in the TRY/CATCH error handling model: FINALLY.

And finally (no pun intended), some housekeeping

The current version of Denali's Books Online indicates that RAISERROR is deprecated. From the RAISERROR (Transact-SQL) topic (http://msdn.microsoft.com/en-us/library/ms178592(SQL.110).aspx):

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. New applications should use THROW instead.

Books Online is currently incorrect, and should be updated soon. There is currently no publicized plan to remove RAISERROR; what the topic should state is that only the old-style RAISERROR is deprecated, e.g.:

RAISERROR 54321 N'This is an old-style RAISERROR.';

Of course you can still use this syntax today, but it has been on the deprecation path for some time. If you run the above code on Denali, SQL Server 2008 R2 or even SQL Server 2008, you will see this performance counter increase:

SELECT [object_name], instance_name, cntr_value
    FROM sys.dm_os_performance_counters
    WHERE [object_name] LIKE N'%:Deprecated Features%'
    AND instance_name = N'Oldstyle RAISERROR'
    AND cntr_value > 0;

TSQL TRY…CATCH

ejohnson2010 — Tue, 12 Oct 2010 17:46:00 GMT

Transact-SQL is a great language for data manipulation, but it has its weaknesses. Unlike “real programming languages” T-SQL is confined to procedural code. Sure, you can build “modules” by using stored procedures and functions, but for the most part, all of the work will be procedural. It has in the past also lacked error handling syntax leaving you with the need to write GOTO statements and labels to control the flow. Well, if you hadn’t noticed, SQL Server 2005 introduced TRY…CATCH blocks to T-SQL. While the implementation in T-SQL is not as robust as that in the object-oriented languages, it’s a good start and its better than GOTO statements. Let’s take a look at how Try...Catch works. Basically, you wrap some portion of your T-SQL code in a TRY block and handle any errors that occur in a CATCH block as shown below.

BEGIN TRY

SELECT * FROM dbo.SALES

SELECT 1/0

END TRY

BEGIN CATCH

PRINT ERROR_MESSAGE()

END CATCH

In the example, we have two select statements in our TRY block, and if either of these encounter an error, control will be passed to our CATCH block. In this case, the SELECT 1/0 should cause a Divide by Zero error and pass control to our CATCH block. Once in the CATCH block, you have access to several functions which will provide details of the error.

ERROR_MESSAGE()
ERROR_NUMBER()
ERROR_SEVERITY()
ERROR_STATE()
ERROR_PROCEDURE()
ERROR_LINE()

These functions can be used to log details of the error that occurred or simply return information to the user that executed the code. If your TRY block completes without error, then control will be passed to the first line after your CATCH block.

That’s pretty much it. There is some nesting ability with TRY…CATCH but you won’t see any other control flow options like RESUME or THROW like you would in the other programming languages. It’s certainly not a perfect solution, but it is a start.

Connect Digest : 2010-01-22

AaronBertrand — Fri, 22 Jan 2010 19:47:00 GMT

Give us easier to read execution plans

Michelle Ufford (@SQLFool) recently asked for help pinpointing the most expensive node(s) in a complicated execution plan. Mladen Prajdic (@MladenPrajdic) has a useful workaround; he coded up a quick query to parse the showplan XML and order results by cost descending. The Connect item that would make this workaround unnecessary was filed by "Ewan1":

#477390 : Rank cost of graphical execution plan components in SSMS

Give us more SARG intelligence in the optimizer

As Adam Machanic (@AdamMachanic) lays out in a recent blog post, the optimizer is getting better at this with each new version, but in this Connect item, Rob Farley (@Rob_Farley) correctly points out that there are a lot of places where non-sargable arguments could obviously (and automatically) be made sargable.

#526431 : Make more functions SARGable

Give us more Agent smarts : system procedures to avoid ad hoc queries

Dave Ballantyne (@DaveBally) blogged that SQL Agent is still very stubborn about submitting ad hoc queries all day long when, in reality, there should be a whole bunch of procedures in msdb to satisfy these queries (and prevent unnecessary ad hoc cache bloat) He also filed this Connect item:

#526485 : dm_exec_cached_plans Bloat

Give us more power to work with data in the results pane

After some back-and-forth with Buck Woody (@BuckWoody), I filed this suggestion, which asks for the ability to embed Excel in the results pane of Management Studio. This would allow us much more immediate analysis of results, with fewer steps.

#524769 : SSMS : Ability to embed Excel in results pane

Give us better error messages

Since I work with multiple schemas, I am coming across more and more error messages from the engine that were obviously written at a time when everything was owned by dbo. As the product gets more complex and the schema model takes hold, this will need to be corrected.

#525308 : All error messages that include object name need to also include schema name

Give us more predictable function performance

Simon Sabin (@Simon_Sabin) and Andrew Novick had similar suggestions: to finally fix the abysmal performance of UDFs that has been present since they were first introduced to the product.

#524983 : User defined function performance is unacceptable
#273443 : The Scalar Expression function would speed performance while keeping the benefits of functions

Give us more complete metadata about stored procedures

Greg Low (@GregLow) came up with an interesting suggestion about providing better metadata about stored procedures (a "contract"). I don't agree with all of Greg's ideas (see recent threads here and here), but in general I agree that the metadata for stored procedure interfaces could be exposed better - providing benefits all around, but especially in environments where a consistent standard is enforced (e.g. resultset shape is not only deterministic but also remains consistent even when inputs do change).

#525653 : Stored procedures should expose detailed contracts

Connect Digest : 2009-10-30

AaronBertrand — Fri, 30 Oct 2009 15:50:00 GMT

Light helping this week. I am busy closing out a few projects (or at least chapters within projects) in preparation for PASS next week.

========================================

#506453 : Lock escalation no longer kicks in for INSERTs in SQL Server 2008

Adam found a nasty lock escalation bug in SQL Server 2008. This one is actually serious enough that it has me really hesitant about my plans for moving to the new version in production, as we process a lot of data in bulk operations like this (loading millions of rows into staging tables, then inserting/updating existing tables).

========================================

#503207 : Bad query plan leads to wrong results (incorrectly uses indexed view)

In this one there is an indexed view matching problem which can produce incorrect results. The fix is in SP1 CU1, so this isn't a "go vote for this issue" mention, but rather encouragement to get up to SP1 + the latest CU (which was Cumulative Update 4 at the time of writing) if you're currently sitting at SP1 or lower.

========================================

#504631 : add RAISE syntax for propagating exceptions

#127228 : Rethrow last error

#125719 : Support Reraising Of Original Error In Catch In T-SQL

A lot of people want RERAISE() and/or RETHROW() among other enhancements to error handling. They are definitely looking at these changes for future versions of SQL Server, so have your opinion heard!

Connect digest : 2009-10-17

AaronBertrand — Sat, 17 Oct 2009 18:12:00 GMT

I've been fairly distracted this past week with my "Bad habits to kick" series, so apologies for being so late with the Connect digest. I stumbled upon a few pretty interesting items from the past two weeks; I hope you find them interesting as well.

#498082 : Allow direct usage of scalar functions in RAISERROR()

It has bugged me that in order to show the original message in, say, a CATCH block, I have to assign ERROR_MESSAGE() to a variable first, since trying to call ERROR_MESSAGE() within RAISERROR() yields an error. I agree with this suggestion that we should be able to reference scalar functions within RAISERROR() (or some function that replaces RAISERROR() in the future).

#498085 : END TRY throws an error if terminated with a semicolon

A weird inconsistency in TRY / CATCH syntax means that you can place a statement terminator on BEGIN TRY, BEGIN CATCH, and END CATCH; however, if you add a semi-colon to END TRY, you get an error message. I am a big fan of using the semi-colon, and I knew there was a reason I don't bother doing so on my TRY / CATCH statements. I must have received this error early on and just assumed it wasn't "proper."

#498009 : Allow filtered unique index to be a candidate key for a foreign key

Now that we can have a true unique constraint that allows multiple NULL values (since a filtered index can be written such that NULLs are not included), Denny Cherry feels that we should be able to provide a foreign key reference to the values in the filtered index.

#497345 : SSMS add Query EndTime next to Execution Duration in the Query Window

While my comment on this item indicates that there is a relatively easy workaround (just hit F4 and view the Properties pane), I do agree that it could be useful to have items like EndTime in the status bar of a query window. I think the display of these various things around the UI (SSMS status bar, query window status bar, tabs, title bar) should be a lot more flexible.

#496617 : "Format Document" in SSMS

Jamie Thomson suggests that Management Studio have a built-in code formatter, along the lines of what we see in Visual Studio. As long as it is flexible enough to format code the way *I* like it, I agree. Yes, there are several 3rd party products that do a decent job of this, however I work on different machines on different days, in different physical locations, and often in several different VMs. Since these tools are licensed per client, it would be pretty expensive for me to license them on every instance of SSMS I use in a typical week.

#496380 : Enable SQL Developer Edition to target specific SQL version

Because Developer Edition supports all of the same features of Enterprise Edition, it is very easy to accidentally use a feature during development that you actually won't be able to use when you deploy, since production is some other edition (Standard, Workgroup, Web, Express). It would be really nice to get some kind of error or warning when you use a feature in Developer Edition that is not supported by the edition(s) you are planning to deploy to.

Gracefully Handing Task Error in SSIS Package

RMehta — Thu, 24 Apr 2008 11:46:00 GMT

I am sure that there is already some blog or article about this. But, I encountered this today and decided to go ahead and blog anyway. I had a situation, where I expected at certain times (the first of every month), a task to fail, but regardless, I wanted the package to continue to run with success. One other issue, I had, was that I did not want to Error Handler of the package to kick off in case of this task failure. Also, since the task was in a Sequence Container, it was causing the Sequence Container execution results to be Failure thus invoking the "On Failure" workflow.

This is the default behavior where the task failure for the “Drop active jobs partition” SSAS DDL task invokes the error handler at the package level (which is where I have a global error handler) and invokes a failure of its parent container. Since I know that this task is bound to fail the 1^st of every month (In this scenario, I am dropping, creating and processing a monthly SSAS partition on a daily which won’t exist on the 1^st of a given month), I want to gracefully “ignore” this failure and not signify a failure or write error rows in my error log tables. So, the way to handle this scenario was..

1. Create an “On Error” Event Handler on the task “Drop active jobs partition” – You can leave the event handler blank with no tasks

2. To Prevent the error from going further up the chain, open up the System Variables from within the On Error error handler of the task and change the Propogate property’s value to False

This should provide the desired result where the error is contained within the task

Optionally, you can also choose to set the ForceExecutionResult property of this task to be Success so that it always signals a success – Even with this, you still need to follow the above steps to prevent errors from bubbling up in the package.