In this chapter, you will learn how to:
Add batch control
Create step and package success and error logging tasks
Record the number of rows transformed and the number of rows with errors
In this chapter, you will learn how to add batch control to the data movement application, which enables you to identify the rows affected by a particular execution of the application. In the absence of transaction control, you might run into a situation in which some subpackages succeed and some fail. Currently, you re able to fix the problem that caused the error and re-execute selected packages. However, on some occasions, you might need to roll back some or all of the data added by the execution of a particular package. The addition of batch control lets you to accomplish this by generating and then recording a batch ID value with each inserted or updated row in each dimension table.
In this chapter, you will also learn how to use Execute SQL tasks to log package starts, package branching, package completion, and package step errors into a single SQL Server table. Logging all this information into a single table creates an audit trail of the execution history for the data movement application and supplements the logging options provided by DTS. After you execute the data movement application, you can review this table to quickly determine the steps in each package that actually executed without having to parse through each error log. You can easily locate package steps with errors that require closer examination to determine the causes of the errors. In many cases, you will be able to fix the error and re-execute one or more packages in the data movement application to complete the data import without having to roll back the application s entire execution.
Finally, you will learn how to create package steps to record the number of rows transformed and the number of error rows encountered by a given task. By recording this information in the audit table along with step and package execution and error information, you can see a more complete picture of the actions taken by the data movement application for a particular execution. By recording the number of rows with errors, you can easily determine whether you have data that must be cleaned and resubmitted before you process the new data in the data warehouse.