Case Scenario: Troubleshooting and Handling Errors in SSIS Packages

  • Article

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 make sure 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 either 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 email messages that identify the task that failed and describe the error in detail.

< Back      Next >

 

 

© Microsoft. All Rights Reserved.