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?
- 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.
- 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.
- 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.
- 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.