1 / 24

Error Handling in SSIS

Error Handling in SSIS. Reza Rad SQL Server MVP, Author, DW / BI Architect. About Me: Reza Rad. SQL Server MVP Author of some SQL Server BI books DW / BI Architect Trainer Author of SSIS Webcast Series. Agenda. Execution Results and Breakpoints in SSDT

cathy
Download Presentation

Error Handling in SSIS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Error Handling in SSIS Reza Rad SQL Server MVP, Author, DW / BI Architect

  2. About Me: Reza Rad SQL Server MVP Author of some SQL Server BI books DW / BI Architect Trainer Author of SSIS Webcast Series

  3. Agenda Execution Results and Breakpoints in SSDT Control Flow Failure controlling with Precedence Constraint Event Handlers Error Output in Data Flow Logging for Packages Logging for Projects in SSIS Catalog Data Taps www.NetComLearning.com

  4. Execution Results window in SSDT Shows useful information about the package and task that executed Progress Window Useful for Debugging Disable/Enable on Tasks and Containers is helpful for Debugging Executing Tasks or Containers www.NetComLearning.com

  5. Breakpoints in SSDT • Pause package execution at specific point • You can check value of variables at that point of the time with three windows; • Autos • Locals • Watch • The best way of debugging in Control flow in DEV environment www.NetComLearning.com

  6. Hit Count Types www.NetComLearning.com

  7. DEMO: Debugging in SSDT Execution Results/Progress Window Disable/Enable Tasks and Containers Breakpointsand Watch window www.NetComLearning.com

  8. Precedence Constraint in Control Flow • Three types of precedence constraints • Success • Failure • Completion • Combination with Expression • AND / OR for multiple tasks www.NetComLearning.com

  9. Error Configuration in Control Flow • Maximum Error Count • Gets or sets an Integer value that indicates the maximum number of errors that can occur before the DtsContainer object stops running. • FailPackageOnFailure • Gets or sets a Boolean that indicates whether the package fails when a child container fails. This property is used on containers, not the package itself. • true indicates that a failure in the container will set the package execution results to failure. • FailParentOnFailure • Gets or sets a Boolean that defines whether the parent container fails when a child container fails. www.NetComLearning.com

  10. Event Handlers • Objects Hierarchy • Events Container • List of Events • OnPreExecute • OnPostExecute • OnError • … www.NetComLearning.com

  11. OnError Event Handler • ErrorCode, and Error Description System Variables • Useful for logging exact error message www.NetComLearning.com

  12. DEMO: OnError Event Handler www.NetComLearning.com

  13. Error Logging in Data Flow • Most of Data Flow components provides an specific output for ERROR OUTPUT • Error Output contains bad data rows, with additional columns; • ErrorColumn • ErrorCode • ErrorDescription; Can be fetched based on ErrorCode www.NetComLearning.com

  14. Fetch Error Description • Error Code is a decimal code, it can be converted to hex, and after 8 F the code will point to an entry of dtsmsg.h file in this path: • C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Include • Or script component can be used for getting the error message in this way: Row.ErrorDescription = ComponentMetaData.GetErrorDescription(Row.ErrorCode); www.NetComLearning.com

  15. DEMO: Error Output in Data Flow www.NetComLearning.com

  16. Legacy Package Logging Design at Development time, configure at runtime Fie types of logging providers supported www.NetComLearning.com

  17. SSIS Catalog Logging 2012 Introduced SSIS Catalog SSIS Projects deploy to SSIS Catalog Logging data will be stored in Catalog Logging configure and set up at run time There are four types of logging Catalog Reports www.NetComLearning.com

  18. Catalog Logging Modes www.NetComLearning.com

  19. DEMO: Logging www.NetComLearning.com

  20. Data Taps • Stores data at specific point of the time (in a Data Flow’s Data Path) into a text file • Very useful way of logging data rows in production environment • You can address any data path in the data flow • Output Text file will be stored in • C:\Program Files\Microsoft SQL Server\110\DTS\DataDumps www.NetComLearning.com

  21. DEMO: Data Tap www.NetComLearning.com

  22. Summary Execution Results and Breakpoints in SSDT Control Flow Failure controlling with Precedence Constraint Event Handlers Error Output in Data Flow Logging for Production and Live environments Data Taps for Live environment www.NetComLearning.com

  23. Summary • Foreach Loop empowers ETL design • Reduces redundant work • Reduces maintenance and support costs • Improves consistency and standardization • Empower Foreach Loop with Expressions and Variables www.NetComLearning.com

  24. Thank you..Stick Around for Q&A www.NetComLearning.com

More Related