Chapter 9: Detecting and Handling Processing Errors

 Download CD Content

Overview

After completing this chapter, you will be able to:

• Understand basic methods of error detection and handling.

• Create and configure event handlers to respond to events that are raised at run time.

• Use transactions to maintain data integrity.

• Use checkpoint restarts to restart failed packages.

In Chapter 8, "Managing Package Execution," you learned how to monitor and update the values of properties at run time without having to modify the actual package. You also learned several ways to execute a package and how to use Microsoft SQL Server Integration Services (SSIS) logging to monitor package execution. In this chapter, you'll learn how to detect and handle the processing errors that can occur when packages are designed and executed. You'll also learn how to create and configure event handlers to develop control flow that responds to events as a package executes, including how to provide more sophisticated error handling. Finally, you'll learn how to use transactions to maintain data integrity and checkpoint restarts to restart failed packages.

In the beginning of this chapter, the exercises will show you how to use event handlers to perform custom logging, one of many possible uses for event handlers. You'll learn how to create an event handler and then add an Execute SQL Task to it, which will be used to log pre-execute events. You'll then create additional event handlers to log post-execute and error events. In the exercises later in the chapter, you'll test the package with invalid data, create an event handler to fix the problem, and then, once again, execute the package. Finally, you'll learn how to send files with incorrectly formatted data into an Error folder without disrupting the execution of the rest of the package.