CHAPTER 2 Debugging and Error Handling in SSIS

Chapter Summary

  • When you run packages in the BIDS SSIS Designer, the debug environment shows the execution status of tasks, the row counts of transformations, and the execution results.
  • SSIS includes package logging that can capture events during the execution of a package such as errors, warnings, and start times and end times for troubleshooting packages when a failure occurs outside of BIDS.
  • The control flow includes flexible precedence constraints to handle success, failure, and completion workflows and more complicated expression and logical AND/OR precedence. In addition, event handlers give you the ability to run processes when conditions that you have defined are met.
  • The data flow allows routing of failed rows out an error path and includes data viewers that give you the capability to observe the data during execution as you are developing a package.
  • By using breakpoints during package debugging, you can pause a package during execution to observe package state information so that you can troubleshoot potential problems.

Lesson 1: Configuring Package Transactions and Checkpoints

1 . you add a Sequence Container to a package that contains several tasks, one of which calls a command on a legacy system and another of which is a Data flow Task imports data into SQL Server. Both tasks have the Transactionoption property set to Required. Even with the MSDTC service started and transactions turned on, your Sequence Container fails before the tasks even run. What is the problem?

The transactions featured in SSIS use the MSDTC service. However, not all systems support MSDTC, and a transaction cannot be forced on a noncompliant system, so the container will fail. you should remove the legacy task from the Sequence Container that has the transaction or set the Transactionoption property to not Supported.

2 . What must you set to use checkpoint properties at the package level after you have turned on the checkpoint properties?

you need to set the failPackageonfailure property to True for tasks to write to the checkpoint file. However, if you want to rerun any successful tasks that occur before the failed task, you need to use a Sequence Container around the group of related tasks that require transactions.

Lesson 2: Identifying Package Status, Enabling Logging, and Handling Task Errors

1 . When a package fails while you are developing it, where should you look to identify what happened?

The Progress or Execution Results tabs in the SSIS Designer show package execution details, including any warnings that were displayed or errors that occurred during execution. often, you will need to scroll through the results and look for the errors and their descriptions. A single error might produce multiple error messages.

2 . you have a package that includes a step that occasionally fails because of network connectivity problems. When a network connectivity error occurs, you need to perform an alternative step to run the same operation in a slower but more reliable way. At the completion of the alternative step, you would like to run the next step in the original workflow. How can you accomplish this?

From the first task, create a red failure precedence constraint to the alternative task. you then need to create Success constraints from both the alternative task and the original task to the third task. you need to set the Success constraints to Logical OR so that when either the first task or the second task is successful, the final task will run.

Lesson 3: Handling Data Flow Errors and Debugging

1 . A Data Conversion Transformation is failing in the middle of the data flow execution, and you need to determine what is causing the error. How should you proceed?

To determine what is causing the error, configure the Data Conversion Transformation error path to flat file so that any rows that are failing conversion are sent to a file. Then create a data viewer on the error path, and run the package in BIDS. This technique will capture the errors in a file and display the rows in the SSIS Designer for troubleshooting.

2 . your package contains a string variable that you are updating, using a Script Task, to be a file path and file name. your package is failing at a file System Task that is configured to use the variable to move the file to a different folder on the server. How do you troubleshoot the package?

Because the Script Task can contain embedded breakpoints in the code, set a breakpoint in the script so that you will be able to execute the package and step through the lines of code, observing the value of the variable to check the code and accuracy.

3 . you would like to log all the package errors to a custom database table that you have created for auditing purposes. How can you accomplish this task?

By using the OnError event handler assigned to the package level, you can also use an Execute SQL Task that calls a stored procedure, passing in the Source-Name and ErrorDescription variable values. The procedure can then track these details into a metadata storage table for auditing.

Troubleshooting and Handling Errors in SSIS Packages

Case scenario

You are creating a set of SSIS packages that move data from a source transactional system to data mart tables. As you develop the packages, you need a way to troubleshoot both your control flow development and your data flow development. You also need to ensure that the data in your destination database is in a consistent state and not in an intermediate state when an error occurs. In addition, you need to provide an audit trail of information and build alerts into your package design. How would you handle the following requirements during your package development and implementation?

  1. In SSIS, you need to use debugging techniques in the control flow and data flow to speed up package development and troubleshooting so that you can complete your packages quickly with minimal frustration.
  2. Each destination table in your data mart must have the inserts, updates, and deletes fully complete and committed, or you need to roll back the changes so that the table is in a consistent state. You also need a way to restart your packages from the point of failure.
  3. You need to capture both the count of rows that are inserted into the destination within your data mart and the time when the last row was sent to each destination in the data flows.
  4. When a package fails, you must immediately send e-mail messages that identify the task that failed and describe the error in detail.

Answers

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.

2. Because 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. 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.

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.