Working with Exception Files


In Chapter 3, you learned how to enable package execution and error logging to assist you in package debugging and auditing. However, these logs contain information only about the success or failure of individual packages. If you want to record package and step execution information, error information (such as the error number and its description), and the actual source and destination rows that generate errors, you must configure exception files for each transformation task (any task that uses the data pump). These files record these additional details with only a small performance penalty.

In DTS Designer, on the Options tab for the transformation task, you can define and configure three different exception files. Figure 5-5 displays the Options tab for the Load CustomerStage Table step in the UpdateCustomerDim package.

click to expand
Figure 5-5: Options tab for the Load CustomerStage Table step in the UpdateCustomerDim package

The first file that you define is a general error information text file that records step execution and error information, including any SQL exception errors that are raised during the package execution. If you choose to use the Microsoft SQL Server 2000 format rather than the Microsoft SQL Server 7 format, you have three additional options. The first option lets you choose whether to record the text of any error messages in the main exception file. The error description and number would be identical to what you would receive if you executed the SQL statement directly via a Transact -SQL script in SQL Query Analyzer. The second and third options let you choose to have the actual source and destination error rows recorded in separate text files. These text files are created only if errors are detected in source or destination rows. Figure 5-6 displays the exception file generated when the UpdateCustomerDim package executes and then encounters an error during the transformation of one of the 30 rows being inserted into the destination table.

click to expand
Figure 5-6: Exception file generated when the UpdateCustomerDim package encounters an error

Notice that the exception file in Figure 5-6 indicates that a null constraint violation occurred when DTS attempted to insert a row into the CustomerCode column. This exception file also indicates that both the source row and the destination row were logged. The source row was logged to the UpdateCustomerDimExceptionFile.txt.Source file, and the destination row was logged to the UpdateCustomerDimExceptionFile.txt.Dest file.

Important  

Destination rows that are rejected by the data destination are recorded in the destination exception file. A row is not logged when DTS detects the error before the row is submitted to the data destination. For example, if an input row contains missing or incorrectly formatted data, the transformation task will fail the row, and it won t pass it to the data destination. The row containing the failure is counted toward the maximum number of errors permitted by the task. However, data integrity violations, such as duplicate keys or foreign key constraint violations, cannot be detected by DTS and are detected only after the destination rows are passed to the data destination. These types of errors are noted in the main exception file and recorded in the destination exception files.

You will incorporate this ability to capture rows of data that cannot be inserted or updated properly into the data movement application prototype. This will enable you to fix errors in these rows and resubmit them.

Note  

If you skipped Chapter 4, execute the IfYouSkippedChapter4.cmd batch file. This batch file restores the SBS_OLTP and SBS_OLAP databases and copies the DTS packages that would have been created in Chapters 1 through 4 into the appropriate folders. If you do not want this batch file to overwrite any packages that you created in Chapters 1 through 4, you must move them or rename them before you execute this batch file.

Define exception files

In this procedure, you will define exception files for the transformation tasks in the UpdateCustomerDim and UpdateProductDim packages.

  1. Switch to SQL Server Enterprise Manager.

  2. In your local SQL Server instance, right-click Data Transformation Services, and then click Open Package.

  3. Navigate to C:\Microsoft Press\SQL DTS SBS\DataMovementApplication in the Look In list, and then double-click UpdateProductDim.dts.

  4. Double-click UpdateProductDim in the Select Package dialog box, type mypassword in the Password text box, and then click OK.

  5. On the design sheet, double-click the Load ProductStage Table step, and then click the Options tab.

  6. Type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\LoadProductStageTableExceptionFile.txt in the Name box.

  7. Clear the 7.0 Format check box, select the Error Text check box, select the Source Error Rows check box, select the Dest Error Rows check box, and then click OK.

    click to expand
  8. On the design sheet, double-click the Insert Or Update ProductDim Table step, and then click the Options tab.

    Tip  

    You can configure exception files for any task that uses the data pump, namely the Transform Data and the Data Driven Query tasks. You cannot configure an exception file for Execute SQL and Bulk Insert tasks.

  9. Type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\LoadProductDimTableExceptionFile.txt in the Name box.

  10. Clear the 7.0 Format check box, select the Error Text check box, select the Source Error Rows check box, select the Dest Error Rows check box, and then click OK.

    click to expand
  11. On the toolbar, click Save and then close the UpdateProductDim package in DTS Designer.

  12. In the SQL Server Enterprise Manager console tree, right-click Data Transformation Services, and then click Open Package.

  13. Navigate to C:\Microsoft Press\SQL DTS SBS\DataMovementApplication in the Look In list, and then double-click UpdateCustomerDim.dts.

  14. Double-click UpdateCustomerDim in the Select Package dialog box, type mypassword in the Password text box, and then click OK.

  15. On the design sheet, double-click the Load CustomerStage Table step, and then click the Options tab.

  16. Type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\LoadCustomerStageTableExceptionFile.txt in the Name box.

    Notice that the default number of permitted errors is zero in the Max Error Count box.

  17. Clear the 7.0 Format check box, select the Error Text check box, select the Source Error Rows check box, select the Dest Error Rows check box, and then click OK.

  18. On the toolbar, click Save.

Now that you have configured exception files, you are ready to modify the NewCustomers.txt file to demonstrate the use of exception files.

Modify the NewCustomers.txt file

In this procedure, you will introduce errors into the NewCustomers.txt file to generate errors when this data is loaded into the CustomerStage table.

  1. Open the C:\Microsoft Press\SQL DTS SBS\Ch4\ChapterFiles\NewCustomers.txt file in Microsoft Notepad.

    This file contains 29 rows of new customer data, with each column separated by a vertical bar.

  2. Delete the first column of data from the sixth and fourteenth rows, and then save and close this changed file.

    The deletion of the data in the first column for these two rows of data will cause null constraint violations in the data destination when these rows are submitted to the data destination.

    click to expand

Now that you have modified the NewCustomers.txt file, you are ready to test the execution of the UpdateCustomerDim package with this source file.

Test the execution of the UpdateCustomerDim package with the modified source file

  1. Switch to SQL Query Analyzer, and then click Clear Window on the toolbar.

  2. Type DELETE FROM SBS_OLAP.dbo.CustomerStage in the query pane, and then click Execute on the toolbar.

    This deletes all existing data in the CustomerStage table, so you can easily see the effect of importing data containing errors.

  3. Switch to the UpdateCustomerDim package in DTS Designer.

  4. On the toolbar, click Execute to attempt to import new customers from the newly modified NewCustomers.txt file into the CustomerStage table.

  5. Click OK to acknowledge that one task failed during execution.

    click to expand
  6. Double-click the Load CustomerStage Table step in the Status window to view the error message.

    The task failed because the first column in the destination table contains a NOT NULL constraint and the first column in two of the data rows being inserted contains a null value. Notice the error message states that the number of failing rows exceeds the maximum specified.

    click to expand
  7. Click OK and then click Done.

  8. Switch to SQL Query Analyzer, and then click New Query on the toolbar.

  9. Type SELECT * FROM SBS_OLAP.dbo.CustomerStage in the query pane, and then click Execute on the toolbar.

    No rows were added to the CustomerStage table because the maximum errors permitted by the Load CustomerStage Table step is zero, and the step uses the default batch size , which processes all rows in one batch.

    click to expand
  10. Use Notepad to open LoadCustomerStageTableExceptionFile.txt in the C:\Microsoft Press\SQL DTS SBS\DataMovementApplication folder.

    This main error file recorded the error information. Notice that no rows were copied from the data source to the data destination because the error occurred in the sixth row and the task was set to permit a maximum of zero errors.

    click to expand
  11. In Notepad, open LoadCustomerStageTableExceptionFile.txt.Source in the C:\Microsoft Press\SQL DTS SBS\DataMovementApplication folder.

    The row from the source table that caused the error is displayed. Vertical bars separate each column in this file, and the value for the first column in this error row is a null value, which is not permitted in the destination table.

    click to expand
  12. In Notepad, open LoadCustomerStageTableExceptionFile.txt.Dest in the C:\Microsoft Press\SQL DTS SBS\DataMovementApplication folder.

    The row that DTS attempted to insert into the destination table is displayed. Depending on the transformations performed in the DTS package, the destination row might be significantly different from the source row. This row could not be inserted into the destination table because a null value was provided for the CustomerCode column, and that column does not permit nulls.

    click to expand
  13. Close Notepad.

Now that you understand how the exception files work, you are ready to increase the maximum error count permitted (the MaximumErrorCount property) for the Load CustomerStage Table step and execute the UpdateCustomerDim package.

Execute the UpdateCustomerDim package with a modified MaximumErrorCount property for the Load CustomerStage Table step

  1. Switch to the UpdateCustomerDim package in DTS Designer, and then double-click the Load CustomerStage Table step on the design sheet.

  2. On the Options tab, change the value in the Max Error Count box to 5 , and then click OK to save this change.

    click to expand
  3. On the toolbar, click Execute.

  4. Click OK to acknowledge that one task failed.

    Notice that all 29 rows in the NewCustomers.txt text file were processed by this task, although this dialog box does not indicate how many rows were successfully inserted. Because the maximum error count value is more than the number of errors encountered , the task did not terminate when the first error was detected. Instead, the task completed, reporting that a failure had occurred. Remember that by default, the failure of a task does not cause a package to fail. In this case, you could choose to have this package continue executing other tasks and have the data movement application continue executing other packages despite this error. Because the error rows are captured in the exception files, you can fix the error and reinsert the corrected error rows as a separate execution of the data movement application. In Chapter 8, you will add more complete error handling routines, and in Chapter 10, you will learn how to insert error rows that originally failed.

    click to expand
  5. Click Done, switch to SQL Query Analyzer, and then re-execute the SELECT * FROM SBS_OLAP.dbo.CustomerStage query.

    All rows in the NewCustomers.txt file other than rows 6 and 14 were added to the CustomerStage table.

    click to expand
  6. Using the Window menu in SQL Query Analyzer, switch to the first query.

    The DELETE FROM SBS_OLAP.dbo.CustomerStage query appears.

  7. On the toolbar, click Execute to delete all 27 rows from the CustomerStage table, and then close the current query window without saving the query.

  8. Switch to the UpdateCustomerDim package in DTS Designer, and then double-click the Load CustomerStage Table step.

  9. On the Options tab, change the value in the Max Error Count box to 1 , change the value in the Insert Batch Size box to 5 , and then click OK.

    The Insert Batch Size box sets the InsertCommitSize property for the Load CustomerStage Table step. DTS will commit rows in batches of five until the maximum error count is reached. When the maximum error value is reached, the task will discard any processed rows in the current batch and terminate.

    Important  

    Committed batches will not be rolled back if the maximum error count is reached before all source rows have been processed unless the task is participating in a transaction.

    click to expand
  10. Click Execute on the toolbar.

  11. Click OK to acknowledge that one task failed during execution, and then click Done.

    Notice that although all 29 rows were not processed, you are unable to determine whether some of these rows were processed.

  12. Switch to SQL Query Analyzer and click Execute on the toolbar to execute the SELECT * FROM SBS_OLAP.dbo.CustomerStage script.

    Because the batch size was set to 5 and the maximum error count was set to 1, the first two batches committed before the maximum error count was reached. Rows 12 and 13 in the third batch before the error in row 14 were not committed.

    click to expand
  13. Close SQL Query Analyzer without saving any queries, and then switch to the UpdateCustomerDim package in DTS Designer.

  14. On the toolbar, click Save and then close SQL Server Enterprise Manager.

You have now configured data transformation steps in two DTS packages to capture error rows in exception files, and you ve learned how to use the InsertCommitSize and MaximumErrorCount properties.




Microsoft SQL Server 2000 DTS Step by Step
Microsoft SQL Server 2000 DTS Step by Step (Step by Step (Microsoft))
ISBN: 0735619166
EAN: 2147483647
Year: 2003
Pages: 87
Authors: Carl Rabeler

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net