Recording Completed Rows and Rows with Errors


To more completely understand the actions performed by package tasks , you can record additional information in the audit table, such as the number of rows completed or transformed and the number of rows with errors. To record this information in the AuditEvents table about actions performed by the Transform Data or Data Driven Query tasks, you need to query the DataPumpTask2 object and the DataDrivenQueryTask2 object through the DTS object model. Among the properties of these objects are RowsComplete and RowsInError . The RowsComplete property returns the count of transformed rows, and the RowsInError property returns the number of transformation error rows. To record information about the actions performed by Execute SQL tasks, you can have the Execute SQL task write directly to the AuditEvents table

Modifying the AuditEvents Table to Record Row Processing Information

In the following procedures, you will add columns to the AuditEvents table to store number of rows completed and then the number of rows with errors. In the subsequent procedures, you will modify tasks in the UpdateProductDim and UpdateCustomerDim packages to record row processing information to the AuditEvents table.

Enable rows added and rows with errors to be recorded by adding columns to the AuditEvents table

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

  2. Click Load SQL Script on the toolbar, navigate to C:\Microsoft Press\SQL DTS SBS\Ch8\ChapterFiles, and then open the AddColumnsToAuditEventsTable.sql script.

    This script adds the RowsComplete and RowsInError columns to the AuditEvents table. These columns will store the values for the RowsComplete and RowsInError properties of the DataPumpTask2 and the DataDrivenQueryTask2 objects.

  3. Execute the AddColumnsToAuditEventsTable.sql script to add these columns to the AuditEvents table.

Now that you have created the infrastructure to store the values for the RowsComplete and RowsInError properties of the DataPumpTask2 and DataDrivenQueryTask2 objects, you are ready to add a global variable to the UpdateProductDim package to record the file location for the SBS_OLAP.udl file.

Reporting Row Processing Information by UpdateProductDim Package Tasks

In the following procedures, you will create a global variable in the UpdateProductDim package to store the file location for the SBS_OLAP.udl file. You will then create an ActiveX Script task that will record directly the values for the RowsComplete and RowsInError properties for the DataPumpTask2 object (the Load ProductStage Table step) and for the DataDrivenQueryTask2 object (the Insert Or Update ProductDim Table step) directly into the AuditEvents table.

Store the location of the SBS_OLAP.UDL file in a global variable in the UpdateProductDim package

  1. Switch to SQL Enterprise Manager, and then right-click Data Transformation Services in your local instance.

  2. Click Open package and then open the most recent version of the UpdateProductDim package in the C:\Microsoft Press\SQL DTS SBS\ DataMovementApplication folder using a password of mypassword .

  3. Right-click an open area on the design sheet, and then click Package Properties.

  4. On the Global Variables tab, type gsSBS_OLAP_UDL in the Name column, select String in the Type column, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\SBS_OLAP.udl in the Value column, and then click OK.

You have added a global variable to the UpdateProductDim package that points to the SBS_OLAP.udl file. You will create an ActiveX Script task that will use this global variable to connect directly to the SBS_OLAP database. You are now ready to add steps to the UpdateProductDim package that will insert the rows completed and rows in error values into the AuditEvents table.

Tip  

Since you will be logging the RowsComplete and RowsInError values to the AuditEvents table using an ActiveX Script task, you cannot directly take advantage of the SBSOLAP_AdditionalConnection connection object to connect to the SBS_OLAP database. That is why the gsSBS_OLAP global variable was added to provide the path to the SBS_OLAP.udl file. To centralize the configuration of this path , you could add an entry for gsSBS_OLAP to the PackageGVs table, or you could modify the ActiveX Script task to read the path to the SBS_OLAP.udl file from the UDLPath property of the SBSOLAP_AdditionalConnection connection object.

Record the number of rows completed and rows with errors by the Load ProductStage Table step by adding an ActiveX Script task to the UpdateProductDim package

  1. On the Task menu, click ActiveX Script Task.

  2. In the Description box, type Log Load ProductStage Table Load Rows , and then click Browse.

  3. In the Look In list, navigate to C:\Microsoft Press\SQL DTS SBS\Ch8\ChapterFiles, and then double-click LogUpdateProductDimLoadProductStageTableLoadRows.bas.

    This script queries the RowsComplete and RowsInError properties of the DTSTask_DTSDataPumpTask_1 task (the Load ProductStage Table step) and places these values into variables. Next this script creates a SQL script to insert the appropriate values into the AuditEvents table and then executes this script.

    click to expand
  4. Click OK to save this ActiveX Script task.

  5. On the design sheet, click the ProductStage connection object, and then hold down the Ctrl key and click the Log Load ProductStage Table Load Rows step.

  6. On the Workflow menu, click On Completion.

Now that you have added a step to record into the AuditEvents table the rows completed and rows in error for the Load ProductStage Table step, you are ready to add a similar step for the Insert Or Update ProductDim Table step.

Record the number of rows completed and rows with errors by the Insert Or Update ProductDim Table step by adding an ActiveX Script task to the UpdateProductDim package

  1. On the Task menu, click ActiveX Script Task.

  2. In the Description box, type Log Insert or Update ProductDim Table LoadRows and then click Browse.

  3. In the Look In list, navigate to C:\Microsoft Press\SQL DTS SBS\Ch8\ChapterFiles, and then double-click LogUpdateProductDimInsertOrUpdateProductDimLoadRows.bas.

    This script queries the RowsComplete and RowsInError properties of the DTSTask_DTSDataDrivenQueryTask_1 task (the Insert Or Update ProductDim Table step) and places these values into variables. Next this script creates a SQL script to insert the appropriate values into the AuditEvents table, and then executes this script.

  4. Click OK to save this ActiveX Script task.

  5. On the design sheet, click the Insert Or Update ProductDim Table step, and then hold down the Ctrl key and click the Log Insert Or Update ProductDim Table Load Rows step.

  6. On the Workflow menu, click On Completion.

    click to expand
  7. On the toolbar, click Save, and then close the UpdateProductDim package.

Now that you have added steps to record the rows completed and rows in error for the data load into the ProductStage and ProductDim tables, you are ready to add similar steps for the data load into the CustomerStage and CustomerDim tables.

Reporting Row Processing Information by UpdateCustomerDim Package Tasks

In the following procedures, you will create a global variable in the UpdateCustomerDim packages to store the file location for the SBS_OLAP.udl file. You will then create an ActiveX Script task that will record directly the values for the RowsComplete and RowsInError properties for the DataPumpTask1 object (the Load CustomerStage Table step). You will then modify the stored procedures that insert and update data in the CustomerDim table to query and report the number of rows updated or inserted by the stored procedure to the AuditEvents table.

Store the location of the SBS_OLAP UDL file in a global variable in the UpdateCustomerDim package

  1. In the SQL Enterprise Manager console tree, right-click Data Transformation Services in your local instance.

  2. Click Open Package and then open the most recent version of the UpdateCustomerDim package in the C:\Microsoft Press\SQL DTS SBS\ DataMovementApplication folder using a password of mypassword .

  3. Right-click an open area on the design sheet, and then click Package Properties.

  4. On the Global Variables tab, type gsSBS_OLAP_UDL in the Name column, select String in the Type column, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\SBS_OLAP.udl in the Value column, and then click OK.

You have added a global variable to the UpdateCustomerDim package that points to the SBS_OLAP.udl file. You will create an ActiveX Script task that will use this global variable to connect directly to the SBS_OLAP database. You are now ready to add steps to the UpdateCustomerDim package that will insert the rows completed and rows in error values into the AuditEvents table.

Record the number of rows completed and rows with errors by the Load CustomerStage Table step by adding an ActiveX Script task to the UpdateCustomerDim package

  1. On the Task menu, click ActiveX Script Task.

  2. In the Description box, type Log Load CustomerStage Table Load Rows , and then click Browse.

  3. In the Look In list, navigate to C:\Microsoft Press\SQL DTS SBS\Ch8\ChapterFiles, and then double-click LogUpdateCustomerDimLoadCustomerStageTableLoadRows.bas.

    This script queries the RowsComplete and RowsInError properties of the DTSTask_DTSDataPumpTask_1 task (the Load CustomerStage Table step) and places these values into variables. Next this script creates a SQL script to insert the appropriate values into the AuditEvents table, and then executes this script.

  4. Click OK to save this ActiveX Script task.

  5. On the design sheet, click the CustomerStage connection object, and then hold down the Ctrl key and click the Log Load CustomerStage Table Load Rows step.

  6. On the Workflow menu, click On Completion.

    click to expand
  7. On the toolbar, click Save, and then close the UpdateCustomerDim package.

Now that you have added a task to record the rows completed and rows in error for the data load into the CustomerStage table from the source files, you are ready to add a similar task for the data load into the CustomerDim table from the CustomerStage table. However, because these inserts and updates are performed by stored procedures called by Execute SQL tasks, you cannot query the DTS object model for this information. Rather, you must use traditional Transact-SQL methods to count the number of rows inserted or updated and have the stored procedures insert this value directly into the AuditEvents table.

Report the number of rows inserted or updated by modifying the CustomerDimUpdateValues and CustomerDimInsertValues stored procedures

  1. Switch to SQL Query Analyzer and then click Load SQL Script on the toolbar.

  2. In the Look In list, navigate to C:\Microsoft Press\SQL DTS SBS\Ch8\ChapterFiles, and then double-click AlterCustomerDimValues.sql.

    This script alters the CustomerDimUpdateValues and CustomerDimInsertValues stored procedures by adding an INSERT statement to each that adds a row into the AuditEvents table. This INSERT statement includes a query of the @@ROWCOUNT counter to report the number of rows affected by the previous UPDATE or INSERT statement.

    Tip  

    When a Transact-SQL INSERT or UPDATE statement encounters an error, the statement aborts. As a result, counting the errors encountered by a Transact-SQL INSERT or UPDATE statement cannot be done. To avoid errors from terminating an INSERT or UPDATE statement contained in an Execute SQL task, you can add a step before the INSERT or UPDATE statement to detect and correct errors before the insertion or update occurs. For example, rows with errors can be moved to an error table before the remaining rows are added to the dimension table. You can then correct the rows with errors at a later time and resubmit the corrected error rows as a separate batch.

    click to expand
  3. Execute the AlterCustomerDimValues.sql script to modify these stored procedures.

  4. Close this query window in SQL Query Analyzer, but do not close SQL Query Analyzer.

Now that you have modified these stored procedures and added steps to the UpdateCustomerDim and UpdateProductDim packages to record in the AuditEvents table the number of rows completed and the number of rows in error, you are ready to test the execution of the data movement application.

Testing the Recording of Row Processing Information

In the following procedure, you will execute the LoadHistoricalData package to delete all test data from the dimension tables. You will then execute the MasterUpdate package and its subpackages using the delete all configuration to delete all staging data from the staging tables. Finally, you will execute the MasterUpdate package and its subpackages using the default configuration and then query the AuditEvents table to determine the number of rows inserted into the staging and dimension tables.

Test package execution

  1. In the SQL Server Enterprise Manager, switch to the LoadHistoricalData package in DTS Designer and the click Execute on the toolbar.

    Performing this step truncates all data in the dimension and fact tables, and then reloads the original historical data. By executing this task, it will be easier to observe the effect of executing the UpdateCustomerDim and UpdateProductDim packages again because the test data that you have previously loaded into the dimension tables is deleted.

  2. Click OK and then click Done.

  3. Close the LoadHistoricalData package in DTS Designer and then switch to Windows Explorer.

  4. Navigate to C:\Microsoft Press\SQL DTS SBS\DataMovementApplication, and then double-click Config2.cmd.

  5. After this batch file completes, switch to SQL Query Analyzer.

  6. Change the query to read SELECT * FROM SBS_OLAP.dbo.AuditEvents WHERE BatchID = 7 ORDER BY PackageName, ExecutionDate , and then click Execute on the toolbar.

    This query returns the steps in each package that executed, sorted by PackageName and then by ExecutionDate . You can now quickly determine the number of rows affected by each package as well as the number of error rows. Notice that the Load CustomerStage Table step in the UpdateCustomerDim package encountered two rows with errors. To determine the cause of these errors, you can review the LoadCustomerStageTableExceptionFile.txt, the LoadCustomerStageTableExceptionFile.txt.Source, and the LoadCustomerStageTableExceptionFile.txt.Dest logs in the file system. No errors were encountered loading data in the ProductStage table. In addition, all 27 of the rows in the CustomerStage table were successfully inserted into the CustomerDim table and the three rows in the ProductStage table were successfully inserted or updated in the ProductDim table.

    click to expand
  7. Close SQL Query Analyzer without saving any modified scripts and then close SQL Server Enterprise Manager.

You have successfully queried the DTS Object Model to return information about rows completed and rows affected, as well as used traditional Transact- SQL techniques to return information about rows inserted and updated.




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