Using Checkpoints and Transactions

The purpose of this exercise is to show how to use checkpoints and transactions. In the first set of exercises, you'll simply load the necessary data into the is2005sbsDW and QuickStartODS database tables in preparation for fixing the error that was created in the previous exercises.

Preparing to Use Checkpoints and Transactions to Fix the Error

Before you can use checkpoint files and transactions to fix the error, you need to execute the  PrepLoadDimProd.dtsx package and view the data in SQL Server Management Studio.

In this procedure, you'll execute the PreLoadDimProd.dtsx package and then view the dbo.ProductAttributes, dbo.ProductPrices, and dbo.DimProd tables in SQL Server Management Studio.

Open the PrepLoadDimProd.dtsx Package and View the Tables in Management Studio

1. In Windows Explorer, navigate to the C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap09 folder.

2. Double-click  Chap09.sln to open the solution file in BIDS.

3. In BIDS, in Solution Explorer, right-click  PrepLoadDimProd.dtsx, and then click Execute Package.

   Wait until the package has executed successfully before starting the next step.

   Notice that in the design window, as each task is successfully completed, the color of the task blocks changes from yellow to green. When the package has successfully executed, the task blocks should be green and, at the bottom of the screen, it should read, "Package execution completed. Click here to switch to design mode, or select Stop Debugging from the Debug menu."

   Your screen should look like this:

   

4. On the Debug menu, click Stop Debugging.

5. Open SQL Server Management Studio.

6. In the Connect to Server box, ensure that the Server name is localhost, and then click Connect. Management Studio opens.

7. To view the data in the dbo.ProductAttributes table:

   1. In the left pane, in Object Explorer, expand the Databases folder, expand Quick-StartODS, and then expand the Tables folder.

   2. Right-click dbo.ProductAttributes, and then click Open Table.

      Note 

      If you don't see dbo.ProductAttributes in the Table folder, in Object Explorer, right-click the QuickStartODS database, and then click Refresh.

   3. Wait for the table to populate in the right pane.

      Your screen should look like this:

      

   4. Horizontally scroll through the table and notice that the Color column doesn't contain Silver/Blk.

8. To view the data in the dbo.ProductPrices table:

   1. In the left pane, in Object Explorer, right-click dbo.ProductPrices, and then click Open Table.

   2. Wait for the table to populate in the right pane.

   Your screen now looks like this:

   

   Notice that values in the ListPrice column are prefixed by USD.

9. To view the table in dbo.DimProd:

   1. In the left pane, in Object Explorer, expand the is2005sbsDW database, and then expand the Tables folder.

   2. Right-click dbo.DimProd, and then click Open Table.

   3. Wait for the table to populate in the right pane.

      Your screen now looks like this:

      

   4. Horizontally scroll through the table and notice that the Color column doesn't contain Silver/Blk. Also, notice that the ListPrice column contains numeric data.

Becoming Familiar with the LoadDimProd Package

In this procedure, you'll familiarize yourself with the four tasks on the Control Flow tab (Delete Staging Tables, Delete DimProd, LoadStaging Tables, and Load DimProd) and the properties associated with each. You'll then execute the  LoadDimProd.dtsx package and discover that the Load DimProd task failed because the ListPrice column was incorrectly formatted.

You'll then switch to SQL Server Management Studio and view and refresh the data in the ProductAttributes, ProductPrices, and DimProd tables. The DimProd table is empty because the LoadDimProd task failed and left the data in an inconsistent state.

In BIDS, on the Control Flow tab, you'll create a Sequence container named Process DimProd and then move the Delete DimProd and Load DimProd tasks into this container. You'll also delete the precedence constraint between the Load Staging Tables task and the Load Dim-Prod task and then create a precedence constraint between the Load Staging Tables task and the Process DimProd sequence container.

You'll then execute the  PrepLoadDimProd.dtsx package and find that the package executes successfully. When you switch to SQL Server Management Studio to view the DimProd table, you find that it now has data.

You return to BIDS and execute the  LoadDimProd.dtsx package again, and the Load DimProd task fails again. You discover that because Process DimProd ran as a transaction, Delete Dim-Prod was rolled back, so the table still contained data.

Become Familiar with the Package

1. In BIDS, in Solution Explorer, double-click  LoadDimProd.dtsx to open the package in the design environment.

   Note 

   The LoadDimProd package already exists. In this exercise, you will configure only transactions and checkpoints.

2. In the Control Flow designer, double-click each task to open its editor, and then view the various properties for each task.

   Important 

   Be sure that you click Cancel to close the editor so that you don't actually make any changes to the tasks.

   The following table lists and describes the function of each task:

   Open table as spreadsheet

   Task	Function
   Delete staging tables	Deletes the records from dbo.ProductAttributes and dbo.Product Prices tables
   Delete DimProd	Deletes the records from the DimProd table
   Load staging tables	Reads data from the \Chap09\Data\ProductAttributes.csv file and inserts it into the dbo.ProductAttributes table Reads data from the \Chap09\Data\ProductPrice.csv file and inserts it into the dbo.ProductPrices table
   Load DimProd	Joins data in dbo.ProductAttributes and dbo.ProductPrices tables and inserts it into the dbo.DimProd table

3. In Solution Explorer, right-click  LoadDimProd.dtsx, and then click Execute Package.

   Your screen looks similar to this:

   

   Notice that the tasks that delete the records in the staging tables and the DimProd table and the task that loads the staging tables succeed, but the Load DimProd task fails.

4. Double-click Load DimProd.

   Your screen looks similar to this:

   

   Notice that the Data Flow designer opens and shows that the data flow task successfully selected data from the staging tables but failed when it tried to insert the records into DimProd. The insert failed because the task tried to insert values with the format USD#.00 into the ListPrice column of the DimProd table. This insert failed because the ListPrice column has a numeric data type.

5. On the Debug menu, click Stop Debugging.

6. Switch to SQL Server Management Studio.

7. To view the updated data in the ProductAttributes table:

   1. In Object Explorer, expand QuickStartODS and Tables, right-click dbo.Product-Attributes, and then click Open Table.

   2. To refresh the data in the table, in the Results pane, click the dbo.Product-Attributes tab, and then click the Execute SQL button.

   Your screen now looks like this:

   

   Notice that the ProductAttributes table successfully deleted and then reloaded. Also, notice that the Color column now includes Silver/Blk records.

8. To view the updated data in the ProductPrices table:

   1. In Object Explorer, right-click dbo.ProductPrices, and then click Open Table.

   2. To refresh the data in the table, in the Results pane, click the dbo.ProductPrices tab, and then click the Execute SQL button (exclamation point).

   Your screen now looks like this:

   

   Notice that the ProductsPrices table successfully deleted and then reloaded.

9. To view the updated data in the DimProd table:

   1. In Object Explorer, right-click dbo.DimProd, and then click Open Table.

   2. To refresh the data in the table, in the Results pane, click the dbo.DimProd tab, and then click the Execute SQL button (exclamation point).

   Your screen now looks like this:

   

   Notice that the data in the DimProd table successfully deleted, but the load failed, so the table is empty. The failure of the LoadDimProd package has left the data in an inconsistent state.

10. In BIDS, click the Control Flow tab.

11. On the left side of the screen, click Toolbox.

12. In the Control Flow Items group, click Sequence Container, and then drag it into the design environment.

13. In the design environment, right-click the new Sequence Container task and rename it Process DimProd.

14. Delete the precedence constraint (green arrow) between the Load Staging Tables task and the Load DimProd task.

15. Click the Delete DimProd task and press SHIFT while clicking the Load DimProd task so that both tasks are selected. Drag both tasks into the Process DimProd container. (The precedence constraint between Delete DimProd and LoadDimProd should remain.)

    Your screen should look similar to this:

    

16. Click an empty area of the design pane to deselect the Delete DimProd and LoadDim-Prod tasks.

17. Right-click the Delete DimProd task and click Properties.

18. In the right pane, in the Properties box, locate TransactionOption, and then verify that the column next to it reads Supported.

    Your screen should look like this:

    

19. Right-click the Load DimProd task and click Properties.

20. In the right pane, in the Properties box, locate TransactionOption, and then verify that the column next to it reads Supported.

21. Right-click the Process DimProd sequence container and click Properties.

22. In the right pane, in the Properties box, locate TransactionOption, and then, in the column next to it, click Required.

23. Drag a precedence constraint from the Load Staging Tables task to the Process DimProd sequence container. (Click the Load Staging Tables task, and a new green arrow will appear at the bottom of the box. Drag this new arrow over to the Process DimProd sequence container.)

    Your screen should look similar to this:

    

    Important 

    Before executing the LoadDim Prod.dtsx package, ensure that the Distributed Transaction Coordinator (DTS) service is running on your computer. To determine this, click Start, click Control Panel, click Administrative Tools, and then click Services. In the Services console, locate the Distributed Transaction Coordinator service, and then, in the Status column, ensure that the status is Started. If the service is not running on your computer, in the left pane, click Start The Service. Wait for the process to complete, and then ensure that the status is Started.

24. In Solution Explorer, right-click  PrepLoadDimProd.dtsx and click Execute Package.

    Wait until the package executes.

    Notice that the package successfully executes.

25. Now switch to SQL Server Management Studio so you can view the table.

26. In SQL Server Management Studio, in the right pane, click the Table - dbo.DimProd tab, and then click the Execute SQL button (exclamation point) to refresh the displayed data.

    Your screen should look like this:

    

    Notice that the dbo.DimProd table now has data.

    In the preceding procedures, when LoadDimProd failed, the DimProd table was empty. In the next procedure, you will see that even though Load DimProd fails because transactions were implemented, the DimProd table is no longer empty.

27. In BIDS, on the Debug menu, click Stop Debugging.

28. In Solution Explorer, right-click  LoadDimProd.dtsx and click Execute Package.

    Wait until the package executes.

    Your screen looks like this:

    

    Notice that again the package failed.

29. On the Debug menu, click Stop Debugging.

    Now you need to view the DimProd and Product Attributes data tables in SQL Server Management Studio.

30. Switch to SQL Server Management Studio.

31. To view the data in the DimProd table, in the right pane, click the Table - dbo.DimProd tab, and then click Execute SQL.

    Your screen looks like this:

    

    Because Process DimProd ran as a transaction, Delete DimProd was rolled back, so the DimProd table still contains data. This is the data that was in the table before the package ran.

32. To view the updated data in the ProductAttributes table:

    1. In Object Explorer, right-click the dbo.ProductAttributes table in the QuickStart-ODS database, and then click Open Table.

    2. To refresh the data in the table, in the right pane, click the dbo.ProductAttributes tab, and then click the Execute SQL button.

    Your screen looks like this:

    

    Notice that the ProductAttributes table has Silver/Blk records in it, whereas the DimProd table did not.

Fixing the Error

In the previous exercises, you discovered that the DimProd table successfully deleted, but reload failed. In this procedure, you'll open the ProductPrice table and discover that the dollar amounts are designated using USD, which causes the conversion from Varchar to Money data type to fail. To fix this problem, you enter a new SQL query statement and then execute the LoadDimProd package again. The package fails again because the entire package ran. The ProductPrices table was reloaded, which overwrote the changes you made. This means that you need to restart the package at the point where the error occurred.

Fix the Error

1. To view the updated data in the ProductPrices table:

   1. In SQL Server Management Studio, in Object Explorer, right-click the dbo.ProductPrices table in the QuickStartODS database, and then click Open Table.

   2. To refresh the data in the table, click the Table - dbo.ProductPrices tab, and then click the Execute SQL button (exclamation point).

   Notice that in the ListPrice column, dollar amounts are designated using USD. This causes the conversion from Varchar to Money data type to fail.

2. In Object Explorer, right-click QuickStartODS, and then click New Query.

3. Copy the following SQL script:

    USE [QuickStartODS] UPDATE dbo.ProductPrices SET ListPrice = REPLACE(ListPrice, 'USD', '') 

4. In the right pane, on the localhost.Quic… - SQLQuery1.sql tab, paste the copied script.

5. Click the Execute button to fix the problem.

   A Messages tab appears at the bottom of the screen, indicating the number of rows affected.

   Your screen looks like this:

   

6. To view the updated data in the ProductPrices table:

   1. In SQL Server Management Studio, in Object Explorer, right-click dbo.ProductPrices, and then click Open Table.

   2. To refresh the data in the table, click the Table - dbo.ProductPrices tab, and then click the Execute SQL button (exclamation point).

7. Confirm that the USD prefix has been removed from all values in the ListPrice column.

8. In BIDS, in Solution Explorer, right-click  LoadDimProd.dtsx, and then click Execute Package.

   Your screen looks like this:

   

   Notice that the package fails again. In the preceding procedure, you ran a SQL statement that removed the USD prefixes from the ProductPrices package. However, when the package ran, the Delete Staging Tables task deleted the records from the dbo.ProductPrices table, and the Load Staging Tables task reloaded the dbo.ProductPrices table with data from the  ProductPrice.csv file. This data has the USD prefix on the prices.

9. Switch to SQL Server Management Studio.

10. To view the updated data in the DimProd table:

    1. In Object Explorer, right-click the dbo.DimProd table in the is2005sbsDW database, and then click Open Table.

    2. To refresh the data in the table, click the Table - dbo.DimProd tab, and then click the Execute SQL button (exclamation point).

    Notice that the DimProd table is still populated and that it does not have the Silver/Blk records yet. Because the Process DimProd container ran as a transaction, when the Load DimProd task failed, the Delete DimProd task was rolled back. This means that you need to restart the package at the point where the error occurred. In the next exercise, you will configure a checkpoint that will enable you to do this.

11. Switch to BIDS and, on the Debug menu, click Stop Debugging.

Implementing Checkpoints

In the previous procedure, you discovered that you need to restart the package at the point where the error occurred. In this procedure, you'll configure a checkpoint file to restart the package at this point.

Implement a Checkpoint File to Restart the Package at the Point of Error

1. In BIDS, in Solution Explorer, double-click  LoadDimProd.dtsx to open the package in the designer.

2. On the Control Flow tab, right-click anywhere in the package designer and click Properties.

3. Make sure that the LoadDimProd package is displayed in the drop-down list at the top of the Properties pane.

4. In the Properties list, in the left column, click CheckpointFileName, and then, in the right column, click the ellipses button.

5. The Select File dialog box appears.

6. In the Select File box, navigate to C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap09\Checkpoints.

7. Select LoadDimProd.txt, and then click Open.

8. In the Properties list, in the left column, click CheckpointUsage, and then, in the right column drop-down list, select IfExists.

9. In the Properties list, in the left column, click SaveCheckpoints, and then, in the right column drop-down list, select True.

10. In the package designer, right-click the Process DimProd container, and then click Properties.

11. Make sure that Process DimProd Sequence is displayed in the drop-down list at the top of the Properties pane.

12. In the Properties list, in the left column, click FailPackageOnFailure, and then, in the right column drop-down list, select True.

13. In Solutions Explorer, right-click  LoadDimProd.dtsx, and then click Execute Package.

    Wait until the package executes.

    

    Notice that the package did not execute successfully. The package fails because of the ListPrice column format.

14. On the Debug menu, click Stop Debugging.

15. In Windows Explorer, navigate to C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap09\Checkpoints.

    Notice the checkpoint file named LoadDimProd.txt file that was created in the Checkpoints folder.

16. In SQL Server Management Server, in Object Explorer, right-click QuickStartODS and click New Query.

17. Copy the following SQL script:

     USE [QuickStartODS] UPDATE dbo.ProductPrices SET ListPrice = REPLACE(ListPrice, 'USD', '') 

18. In the right pane, on the localhost.Quic… - SQLQuery1.sql tab, paste the copied script.

19. Click the Execute button to fix the problem.

    Your screen looks like this:

    

20. To view the updated data in the tables:

    1. In Object Explorer, right-click dbo.ProductPrices, and then click Open Table.

    2. To refresh the data in the table, click the Table - dbo.ProductPrices tab, and then click Execute SQL (exclamation point).

21. Confirm that the USD prefix has been removed from all values in the ListPrice column.

22. In BIDS, in Solution Explorer, right-click  LoadDimProd.dtsx and click Execute Package.

23. Wait for the package to execute.

    Your screen looks like this:

    

    Notice that the Delete Staging Tables and Load Staging Tables tasks did not execute. When the LoadDimProd package executed, it found the LoadDimProd.txt file in the Checkpoints folder. Because this file contained information that told the package that the Delete Staging Tables and Load Staging Tables successfully executed while the Process DimProd sequence container failed, the package executed only the Process Dim-Prod sequence container.

24. In SQL Server Management Studio, click Table - dbo.DimProd and click Execute SQL.

    Notice that there are records with Silver/Blk values in the Color column. This means that you have successfully loaded the data from the  ProductAttributes.csv and Product Price.csv files into the DimProd table.