Zen and the SSIS MaximumErrorCount Property

Introduction

Earlier, I acciedntally scheduled this post and it published before I could revise it. I received an interesting comment about that from my friend Mike Walsh – hopefully the comment will survive the editing process.

Prelude

I don’t know much about Zen. I’ve met a few practitioners in my time and respect everyone’s right to believe what they choose. I had an interesting experience related to the practice of Zen while living and working in China during the mid-1990’s, but that’s a story for another post.

My experience with Zen believers suggests offending them would be difficult but I may very well be up to the task! That same experience leads me to believe practitioners of Zen and its teaching are very calm and peaceful individuals. And that’s really what I’m after in this post: calmness, peace, tranquility.

I think all SSIS developers could benefit from more tranquility. And the MaximumErrorCount property is designed to smooth out the bumps in the road; to make everything better.

MaximumErrorCount

SSIS allows you to manage fault tolerance in packages. There’s lots of goodies built in that facilitate fairly granular control. The tough part is learning how they work.

One such property is MaximumErrorCount. This property exists on lots of SSIS objects: the Package, Tasks, Containers, even EventHandlers. What does it do? Like me, you would probably assume it allows the object for which you change the MaximumErrorCount property to continue running even if it encounters errors. And that assumption would be correct.

But…

In SSIS, errors “bubble.” If you’re an old web developer like me, bubbling makes sense. If not, I’ll attempt to explain.

Think about scope. Not the mouthwash, but rather object scope. Object scope can be described using the concept of containers. Containers can hold objects, variables – even other containers. In application development, you can have a class named MyClass with a variable named MyVar. Inside that class, you can define a method named MyMethod (did you guess I was gong to call it that before reading it? Gosh you’re good!) which can also have a variable named MyVar.

They are not the same variable.

They are both named MyVar. They both live inside* the MyClass class. But deep in the heart of the server’s RAM, their values exist in separate and distinct memory locations. They may as well be named x and y.

This can get you in trouble in application coding. And – as SSIS lends itself to application metaphors – it can also get you in trouble in SSIS.

* MyClass.MyMethod.MyVar is technically inside MyClass, although nested. Whereas MyClass.MyVar is a class-scoped variable.

But wait there’s more!

What is an “Error”? 

I can hear you thinking, “Huh?” I understand. It seems like an obvious question, but it’s not.

As I explain to folks when I’m training, SSIS places one foot squarely in the domain of Application Development and another foot squarely in the realm of Database Development. As a result, all that stuff you read about “impedance mismatches in ORMs (Object-Relational Mapping applications)” steps to center stage in SSIS.

 

“How Can This Get Me In Trouble In SSIS?”

I’m glad you asked!