Debugging and Error Handling in SSIS

1. When you are developing in the control flow, you can use breakpoints to pause packages during execution so that you can examine the intermediate state and the results of your tasks and constraints. When you are working in the data flow, you can use data viewers on your data paths and error paths to catch errors, and you can watch the rows to isolate any errors and help determine the best way to fix them.
1a) You should also implement checkpoints on the containers, which will let you restart the packages at the point of failure after you have resolved any problems. You can simplify this implementation by creating a master package that has checkpoints turned on and that uses the Execute Package Task to call child packages for each destination table that has transactions enabled.

2.  the commit level is configured on a table-by-table basis, all the data flow and control flow tasks that operate on a single task need to be grouped together in a container, and the TransactionOption property must be set to Required for each container.

3. To capture the destination row count, you add several Row Count Transformations to your package. Place a Row Count Transformation in the pipeline before each destination.

 The Row Count Transformation will store in a predefined variable the number of rows that flow through the component, so you can create a separate package variable for each destination. To capture the variable values, set the RaiseChangeEvent property to True for all new variables and add the OnVariableValueChange event handler. This event fires when each Row Count Transformation updates the identified variable, which subsequently calls a SQL statement that adds the VariableName, count, and EventHandlerStartTime to a tracking table.

4. Using the OnError event, you create a new event handler on the package executable file level. This event handler contains a single Send Mail Task that you configure to use the SourceName variable, which is the task or container name that experienced the error as the e-mail message subject and the ErrorDescription variable as the e-mail message body. You hard-code your e-mail Simple Mail Transport Protocol (SMTP) server and your support team’s Distribution List (DL) address so that all parties will be e-mailed when a failure occurs.