[go: up one dir, main page]
Scribd
Upload
157 views24 pages

Debugging and Troubleshooting SSIS Packages

This document provides an overview of debugging and troubleshooting SSIS packages. It covers debugging packages by viewing execution events, using breakpoints and data viewers. It also covers logging package events, implementing event handlers to handle errors, and redirecting failed rows in data flows. The lab scenario has students debug an ETL process, log executions, add an event handler and handle data flow errors.

Uploaded by

Richie Poo
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
157 views24 pages

Debugging and Troubleshooting SSIS Packages

This document provides an overview of debugging and troubleshooting SSIS packages. It covers debugging packages by viewing execution events, using breakpoints and data viewers. It also covers logging package events, implementing event handlers to handle errors, and redirecting failed rows in data flows. The lab scenario has students debug an ETL process, log executions, add an event handler and handle data flow errors.

Uploaded by

Richie Poo
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 24

Module 8

Debugging and Troubleshooting


SSIS Packages
Module Overview

Debugging an SSIS Package


Logging SSIS Package Events
• Handling Errors in an SSIS Package
Lesson 1: Debugging an SSIS Package

Overview of SSIS Debugging


Viewing Package Execution Events
Breakpoints
Variable and Status Windows
Data Viewers
• Demonstration: Debugging a Package
Overview of SSIS Debugging

• Debugging During Development


• Observe row counts and task outcome
• View events in the Output window and
Progress/Execution Results tab
• Step through package execution
• Track variable values
• View data in the data flow

• Debugging in the Production Environment


• View package execution logs
• Create a dump file
Viewing Package Execution Events

• Package execution is a sequence of


events generated by tasks and containers
• During debugging, events are shown:
• Progress/Execution Results tab
• Output window
Breakpoints

• Add breakpoints to halt execution when


debugging
• Specify breakpoint conditions:
• Event
• Hit Count

• Manage breakpoints in the Breakpoints window


Variable and Status Windows

• Locals window: shows in-scope variables and


status
• Watch windows: show selected variables
Data Viewers

• Enable data viewers on data flow paths


• View data as it passes through the data flow
• Copy data for further investigation
Demonstration: Debugging a Package

In this demonstration, you will see how to:


• Add a breakpoint
• View variables while debugging
• Enable a data viewer
Lesson 2: Logging SSIS Package Events

SSIS Log Providers


Log Events and Schema
Implementing SSIS Logging
Viewing Logged Events
• Demonstration: Logging Package Execution
SSIS Log Providers

• Windows Event Log


• Text file
• XML file
• SQL Server
• SQL Server Profiler
Log Events and Schema
Log Events Log Schema
• OnError • StartTime
• OnExecStatusChanged • EndTime
• OnInformation • DataCode
• OnPipelinePostComponentCall • Computer
• OnPipelinePostEndOfRowset • Operator
• OnPipelinePostPrimeOutput • MessageText
• OnPipelinePreComponentCall • DataBytes
• OnPipelinePreEndOfRowset • SourceName
• OnPipelinePrePrimeOutput • SourceID
• OnPipelineRowsSent • ExecutionID
• OnPostExecute
• OnPreExecute
• OnPreValidate
• OnProgress
• OnQueryCancelled
• OnTaskFailed
• OnVariableChangedValue
• OnWarning
• Diagnostic
• DiagnosticEX
Implementing SSIS Logging

1. Add and configure log providers


2. Select containers and tasks to include
3. Select events and details to log
4. Override log settings for child executables if
required
Viewing Logged Events

• Logged events are displayed in the Log Events


window
• Even if no log provider is specified
• Useful for:
• Troubleshooting
• Testing a logging strategy
Demonstration: Logging Package Execution

In this demonstration, you will see how to:


• Configure SSIS logging
• View logged events
Lesson 3: Handling Errors in an SSIS Package

Introduction to Error Handling


Implementing Event Handlers
Handling Data Flow Errors
• Demonstration: Handling Errors
Introduction to Error Handling

• Handling errors in control flow


• Failure precedence constraints
• Event handlers

• Handling errors in data flow


• Ignore or redirect failed rows
Implementing Event Handlers

• Add an event handler on the Event Handler tab


• Each event handler has its own control flow
• Use contextualized system variables to
implement custom logging or notifications
Handling Data Flow Errors

• Configure Error Output for data flow


components:
• Fail component
• Ignore failure
• Redirect row

• Redirect failed rows with error output path


• DiagnosticEX event for more detailed error
information
• Use custom ETL data lineage to trace data back
from the destination to the source, retaining all
history of transformations and deduplication
Demonstration: Handling Errors

In this demonstration, you will see how to:


• Implement an event handler
• Redirect failed rows
• Add DiagnosticEX logging
Lab: Debugging and Troubleshooting an SSIS
Package

Exercise 1: Debugging an SSIS Package


Exercise 2: Logging SSIS Package Execution
Exercise 3: Implementing an Event Handler
• Exercise 4: Handling Errors in a Data Flow

Logon Information
Virtual machine: 20767C-MIA-SQL
User name: ADVENTUREWORKS\Student
Password: Pa55w.rd

Estimated Time: 60 minutes


Lab Scenario

The ETL process for Adventure Works Cycles


occasionally fails when extracting data from text
files generated by the company’s financial
accounts system. You plan to debug the ETL
process to identify the source of the problem and
implement a solution to handle any errors.
Lab Review

Having completed this lab, you will now be able to:


• Debug an SSIS package
• Log SSIS package execution
• Implement an event handler in an SSIS package
• Handle errors in a data flow
Module Review and Takeaways

• Review Question(s)

You might also like