SSIS provides us with a number of options for Error handling, at different levels. In this blog, I have tried to describe usage of these options with the help of examples.
These examples have been developed using SSDT 12.0.41012.0 [Visual Studio 2013 Shell (Integrated)].
Error Outputs at Data Flow Level
I start with the option available at data flow level, available to us when we use a Data Flow Task.
We will have at least one source component and at least one destination component.
Data truncations and errors like data type mismatch are most likely to occur both, at source level and at destination level. With thousands of records to be processed, it is impossible for us to manually go through the input and separate the rows, which contain such incorrect data (I refer these rows as erroneous rows in this blog).
SSIS provides us with an option ‘Configure Error Output’ that we can use to redirect the erroneous rows to a separate output. All we have to do is to select the option ‘Redirect Row’ for a column that we suspect can have incorrect data.
To depict this at source level, I used a Data Flow Task and 3 flat file connection managers ,
- A source file (flat file source)
- A destination file (flat file destination)
- An error file.(flat file destination)
The destination file will have the same columns as that of the source file.
The error file will have the following 3 columns by default,
- [Flat File Source Error Output Column]
- [ErrorCode]
- [ErrorColumn]
You will notice that you only get the Id of the column in the ErrorColumn.
SSIS 2016 allows you to get the column name. See here.
The sample source data that I have used is,
To generate error, I configured the source connection manager with an integer ProductSKU (just double click the source connection manager and see ‘Advanced’ tab). You will notice that not all ProductSKUs are integers in the sample data,
Next, I configured the flat file source to redirect erroneous rows for column ProductSKU using ‘Configure Error Output’ (just double click the component and see the ‘Error Output’ tab).
The final screen with all the source(s)/destination(s) configured will look as shown below,
When I run the package, I find that rows with non-integer ProductSKUs have been redirected to the Error Output.
Note: We can also use ‘Data Viewers’ to view data during debugging.
Using Event Handlers
Moving forward to a generic error handling approach, SSIS Events Handlers provide us a very powerful error handling and logging.
All we need to do is to go to the ‘Event Handlers’ tab and generate an event handler for ‘OnError’ event. These handlers can be created at package level or for any of its components. We can then log event in the database or send error details in an email or so.
To depict the use of event handlers I,
- Created an integer variable ‘MyIntegerVariable’
- Assigned a string value to MyIntegerVariable using a Script Task
- Created a package level Event Handler. I just put a Script Task in the event handler.
- And added a break point to it so as to know when the event handler gets called.
- We can see the error using the Quick Watch window (Shift + F9).
Using Expressions in Constraints
Using the above approach will suit our needs most of the time but, it will break the package at the very point it hits an error. What if we want to catch the error but continue execution of the package?
We can use constraints (the green lines that connect 2 components in Control Flow) to achieve this. The steps I followed are,
- Create variable ‘MyErrorCode’
- Modify the Script Task to set MyErrorCode value to 1 if an error occurs. Note that I removed the line ‘Dts.Events.FireError….’
- 2 constraints(arrows) will emerge from this task, one to be followed on success, and one on failure.
- Modified the constraints,
o Set ‘Evaluation Expression’ to ‘Expression and Constraint’ for both the constraints
o Set ‘Expression’ to ‘@[User::MyErrorCode] == 0’ for the success constraint
o Set ‘Expression’ to ‘@[User::MyErrorCode] == 1’ for the failure constraint
Having done this, I ensured that the variable ErrorCode was set to 1 if any exception occurred in the Script Task. Then the constraints checked the variable value and decided which task to execute next.
In our case, since the Script Task threw an exception, the Failure Expression Task was executed.
Exploring further.......
SSIS offers certain properties that I haven’t detailed here, but can be very useful when used in combination to some of the above solutions,
- FailPackageOnFailure
- FailParentOnFailure
- MaximumErrorCount
- ForceExecutionResult
- Propagate (system variable)
Hope this helps!
