Working with Transactions

A DTS transaction ensures that all its tasks complete successfully and all task changes are committed; or, if the transaction fails, it rolls back the changes made by any task in the transaction. DTS transactions enable you to keep data consistent across multiple servers, incorporate all tasks in a package into a single transaction, utilize multiple transactions in a single package, and control errors in an asynchronous environment. To automatically roll back changes made by a DTS package whenever a package failure occurs, you must enable and configure transactions at the package level, and then configure tasks to join ongoing transactions when they execute. However, some tasks and some connection objects do not support transactions. Furthermore, you must limit the execution parallelism of your data movement application if you want to incorporate transactions into your application.

Enabling and Configuring Transactions

Packages do not use transactions unless you enable transactions at the package level. You can use DTS Designer to enable a package to use transactions on the Advanced tab of the package s DTS Package Properties dialog box. Figure 5-4 displays this dialog box for the MasterUpdate package.

Figure 5-4: The Advanced tab of DTS Package Properties dialog box for the MasterUpdate package

After you enable a package to use transactions, you have access to two transaction configuration options. Your first option allows you to choose whether to commit an open transaction when the package completes successfully. At the task level, you can also choose to have a transaction commit whenever a particular step succeeds. If a package completes successfully with an uncommitted transaction, you must select the Commit On Successful Package Completion check box on the Option tab in the Workflow Properties dialog box, or else the uncommitted transaction will be rolled back with all pending changes lost.

A list box option on the Advanced tab for the package lets you configure the transaction isolation level used for every task that joins the package transaction. The isolation level controls the extent to which data in other transactions that have not been committed is visible to the transaction (including other transactions in the package as well as transactions started by other users of the data source). Transaction isolation levels include the following:

• Read Committed A package operating at this isolation level cannot see uncommitted changes to data made by other transactions but can incur nonrepeatable or phantom reads. A nonrepeatable read is a read of one or more rows of data within a transaction that, when repeated, yields different values for the same rows. A phantom read is a read of data on one or more rows within a transaction that, when repeated, yields a different number of rows. Read Uncommitted is the default isolation mode for all SQL Server operations, including DTS.

• Repeatable Read A package operating at this isolation level cannot see uncommitted changes to data made by other transactions and does not permit changes to the rows previously read within the transaction until the transaction completes. However, new phantom rows can still be inserted.

• Serializable A package operating at this isolation level cannot see data in uncommitted changes to data made by other transactions, does not permit changes to rows previously read within the transaction, and does not permit the insertion of new rows into the data set.

• Read Uncommitted A package operating at this isolation level can see uncommitted changes to data made by other transactions and might also incur nonrepeatable or phantom reads. This isolation mode is similar to the Chaos isolation level but is supported by SQL Server.

• Chaos A package operating at this isolation level can see uncommitted changes to data made by other transactions. Changes made to data by a package operating at this isolation level cannot be rolled back. This isolation mode is not allowed by SQL Server but is permitted by non “SQL Server data sources. No locks are held by transactions running at this isolation level.

For more information on isolation levels other than Chaos, see SQL Server Books Online.

Important  

The Microsoft Distributed Transaction Coordinator (MS DTC) service must be running on the computer on which a package is executing in order for DTS packages to use transactions. For some data sources such as Oracle, additional services or configuration might be necessary for the package to enlist the data source in a transaction.

Enlisting Tasks in Transactions

A package transaction begins when a task in a package attempts to join a transaction (provided the package is configured to use transactions). If an ongoing transaction exists, the task joins the open transaction. If no ongoing transaction exists, a new package transaction is started. Only one transaction can be active in a package at any one time. In DTS Designer, on the Options tab of a task s Workflow Properties dialog box, you can enable a task to join a transaction if one is present. On this same tab, you configure whether an ongoing transaction is committed when the task completes successfully or is rolled back when the task fails.

Transactions cannot be used unless the destination connection supports them. The following connection types do support joining transactions:

• Microsoft OLE DB Provider for SQL Server

• ODBC data source, provided the ODBC driver supports the SQL_ATT_ENLIST_IN_DTC connection attribute and this attribute is set

• Microsoft Data Link, provided the OLE DB provider implements the ITransactionJoin interface

  Note  

  A connection to a Microsoft Access connection cannot join a transaction.

In addition, only certain tasks can join transactions. The following tasks can participate in transactions if the destination connection they are using supports joining transactions:

• Transform Data task

• Data Driven Query task

• Execute SQL task

• Bulk Insert task

• Execute Package task

• Message Queue task

Note that neither the Execute Process task nor the ActiveX Script task can join an ongoing transaction. A transaction can be used within both of these tasks, but the tasks themselves cannot join an ongoing transaction within the package. However, a task that is not part of an ongoing transaction, including a task that cannot join transactions, can be configured to cause an ongoing transaction to commit upon the success of the step or to roll back upon the failure of the step.

Note  

When a step joins a transaction, each connection used by the step joins the transaction. This means that updates for other tasks in the package that use the connection become part of the transaction, even though those tasks did not enlist in the ongoing transaction. Use separate connections to a database to enable both transactional and non-transactional updates to the same database.

Inheriting Transactions

When a subpackage is called from a master package by the Execute Package task, the subpackage will inherit the parent package transaction if the Execute Package task that fired the subpackage joined the parent transaction. When a subpackage runs within an inherited transaction, no commit takes place within the subpackage (although a step failure can still cause a transaction rollback). This means that you cannot cause a transaction to commit when a step in the subpackage, or the subpackage itself, completes successfully. You can, however, cause the transaction to commit when the step that called the subpackage completes successfully.

Note  

A transaction rollback in a subpackage does not cause the subpackage to fail unless you configure the failure of the step to cause package failure. This means that even though a step in a subpackage can fail and roll back the entire open transaction, the subpackage can complete successfully and report this success back to the calling package.

Considerations When Working with Transactions

The next few sections provide guidelines for working with transactions in packages.

Organize the Package Steps Sequentially

Sequentially executing packages simplify your work with transactions. You can use precedence constraints to make all tasks execute sequentially after successful completion of the previous task, cause the failure of any task to roll back the transaction, and ensure the final step in the package commits the transaction when the step completes successfully.

Plan Carefully When Executing Package Steps in Parallel

When executing tasks in parallel, you must take steps to avoid anomalous results. First, use DTS package failure to roll back the transaction in the event that a step fails. Not following this rule can result in steps continuing even though a transaction rolled back when one task in the transaction failed. For example, suppose Task C is configured to execute after the successful completion of Task A, and Task A and Task B execute in parallel within a single transaction. If Task B fails, all changes made by Task A and Task B are rolled back after Task A completes. However, since Task A completed successfully, Task C will commence, starting a new transaction because there is no existing transaction. If the failure of a single task causes the failure of the entire package, no additional steps will start.

When using separate connections to a SQL Server instance, multiple steps cannot execute in parallel within the same transaction. To avoid this problem, you can either use a single connection for each step to serialize the steps or execute each of the separate steps on the main package thread. This problem will not appear with Execute SQL tasks that SQL Server can execute very quickly because they will actually execute serially rather than in parallel.

Branch upon the Success or Failure of a Transaction

As you learned in Chapter 2, you can use precedence constraints to cause a task to execute based on the success, failure, or completion of a previous task. For example, you can configure one or more tasks in a package to execute only after the failure of a previous package step. When the step fails, you can roll back the transaction and use a failure precedence constraint to execute error-handling tasks, such as Send Mail, Execute SQL, and ActiveX Script tasks, to report and record error information.

Use Transaction Checkpointing for Multiple Transactions

If the logic of your package enables you to use multiple transactions in a single package, use an ActiveX Script task as a placeholder, and use precedence constraints to have the tasks in the first transaction trigger the execution of the ActiveX Script task. You can then configure the ActiveX Script task to commit this first transaction upon the successful completion of the ActiveX Script task. The ActiveX Script task itself does not actually execute any script; rather it merely marks the transition between one set of tasks in one transaction and another set of tasks in another transaction.

Note  

Because of the limitations on using transactions with parallel task execution, you will not utilize transactions in your data movement application. Rather, the application will incorporate into its design the ability to partially or completely roll back batches that fail based on a batch ID value.

Test the TransactionDemo package

In this procedure, you will test the TransactionDemo package without transaction control.

1. Open SQL Server Enterprise Manager, expand your local instance, right-click Data Transformation Services, and then click Open Package.

2. In the Look In list, navigate to C:\Microsoft Press\SQL DTS SBS\Ch5\ChapterFiles and then double-click TransactionDemo.dts.

3. Double-click TransactionDemo in the Select Package dialog box.

   This package includes four Execute SQL tasks that execute in sequence. The first task deletes one row from each of three tables (to clean up from a previous execution of this package), and each of the other three tasks adds one row to one of these three tables.

   

4. On the toolbar, click Execute.

5. Click OK, verify that all four tasks executed successfully, and then click Done.

6. Open SQL Query Analyzer and connect to your local instance as a system administrator.

7. On the toolbar, click Load SQL Script, navigate to C:\Microsoft Press\SQL DTS SBS\Ch5\ChapterFiles, and then double-click TransactionTestScript.sql.

   This script queries the ProductStage , CustomerStage , and SalesStage tables to determine whether a particular row has been added to each table.

8. On the toolbar, click Execute.

   One row was successfully added to each table by the TransactionDemo package.

   

9. Switch to the TransactionDemo package in DTS Designer and then double-click the Update SalesStage Table step on the design sheet.

10. Delete the closing parenthesis in the VALUES list in the INSERT statement, and then click OK to save the modified Execute SQL task.

    

11. On the toolbar, click Execute.

12. Click OK to acknowledge that one task failed, verify that the Update SalesStage Table step failed, verify that the other three steps succeeded, and then click Done.

    

13. Switch to SQL Query Analyzer and re-execute the TransactionTestScript query.

    The Delete step successfully deleted the rows previously entered into these tables, but only the ProductStage and CustomerStage tables were repopulated because the Execute SQL task called by the Update SalesStage Table step failed to execute properly.

    

Now that you see how this simple package works in the absence of transaction control, you are ready to add transaction control to the TransactionDemo package.

Enlist tasks that execute sequentially in a single transaction

In this procedure, you will configure four tasks to execute as part of a single transaction.

1. Switch to the TransactionDemo package in DTS Designer.

2. Right-click an open area of the design sheet and click Package Properties.

3. On the Advanced tab, verify that the Use Transactions and Commit On Successful Package Completion check boxes are selected, and then click OK.

   

4. On the design sheet, right-click the Delete step, point to Workflow, and then click Workflow properties.

5. On the Options tab, select the Join Transaction If Present check box, select the Rollback Transaction On Failure check box, and then click OK.

   

6. On the design sheet, right-click the Update CustomerSales Table step on the design sheet, point to Workflow, and then click Workflow properties.

7. On the Options tab, select the Join Transaction If Present check box, select the Rollback Transaction On Failure check box, and then click OK.

8. Right-click Update ProductStage Table on the design sheet, point to Workflow, and then click Workflow properties.

9. On the Options tab, select the Join Transaction If Present check box, select the Rollback Transaction On Failure check box, and then click OK.

10. Right-click Update SalesStage Table on the design sheet, point to Workflow, and then click Workflow properties.

11. On the Options tab, select the Join Transaction If Present check box, select the Rollback Transaction On Failure check box, and then click OK.

Now that you have enlisted these tasks to execute in a single transaction, you need to start the Distributed Transaction Coordinator (if it is not already started).

Start Distributed Transaction Coordinator

1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Service Manager.

2. Verify that your local server appears in the Server list and then select Distributed Transaction Coordinator in the Services list.

3. If the Distributed Transaction Coordinator service is not started, click Start.

   

4. Verify the Distributed Transaction Coordinator started and then close SQL Server Service Manager.

Now that you have started the Distributed Transaction Coordinator, you are ready to test the execution of the TransactionDemo package with transaction control.

Test the TransactionDemo package

In this procedure, you will modify the Update CustomerStage Table step to cause it to fail and observe the consequences now that you have enabled transaction control for the TransactionDemo package.

1. Switch to the TransactionDemo package in DTS Designer and then double-click the Update CustomerStage Table step.

2. Delete the closing parenthesis in the VALUES list in the INSERT statement, and then click OK to save the modified Execute SQL task.

3. On the toolbar, click Execute.

4. Click OK, verify that only the Delete step executed successfully, and then click Done.

   

5. Switch to SQL Query Analyzer and re-execute the query.

   Although the Delete step executed successfully, its actions were rolled back because the package was executed under transaction control. Because all the transactions were part of a single transaction and one of the steps failed, the entire transaction failed. As a result, no rows were deleted.

   

Now that you understand how transaction control works within a package when a single transaction is involved, you are ready to add a second transaction to this package.

Enlist tasks into multiple transactions using checkpointing

In this procedure, you will configure the Delete step to commit the initial transaction when it completes successfully. This means that the subsequent tasks in the package will be part of a second transaction.

1. Switch to the TransactionDemo package in DTS Designer.

2. On the design sheet, right-click the Delete step, point to Workflow, and then click Workflow Properties.

3. On the Options tab, select the Commit Transaction On Successful Completion Of This Step check box, and then click OK.

   

   Tip  

   Although you have now incorporated two transactions within a single package, this fact is not obvious to someone new to the package. To make it more obvious, you could add an ActiveX Script task between the Delete step and the Update CustomerStage Table step as a placeholder and label it as the transaction checkpoint task.

Now that you have enabled the TransactionDemo package to use two transactions, you are ready to test its execution.

Test the TransactionDemo package with two transactions

In this procedure, you will test the execution of the TransactionDemo package to demonstrate how DTS can use two separate transactions within a single package.

1. On the DTS Designer toolbar, click Execute.

2. Click OK to acknowledge that one task failed, verify that only the Delete step succeeded, and then click Done.

3. Switch to SQL Query Analyzer and then re-execute the query.

   The Delete step was not rolled back because the ongoing transaction committed after this step finished and the Update CustomerSales Table step began a new transaction when it started.

   

4. Switch to the TransactionDemo package in DTS Designer and then double-click the Update CustomerStage Table step.

5. Add the closing parenthesis to the VALUES list in the INSERT statement that you removed in a previous procedure, and then click OK.

6. On the toolbar, click Execute.

7. Click OK to acknowledge that one task failed, notice that only the Update SalesStage Table step failed, and then click Done.

   

8. Switch to SQL Query Analyzer and re-execute the query.

   Although the Update CustomerStage Table and Update ProductStage Table steps executed successfully, the actions of those steps were rolled back because the Update SalesStage Table step failed.

9. Switch to the TransactionDemo package in DTS Designer and then double-click the Update SalesStage Table step on the design sheet.

10. Add the closing parenthesis to the VALUES clause of the INSERT statement that you removed in a previous procedure and then click OK.

11. Click Execute on the toolbar.

12. Click OK, verify that all four steps completed successfully, and then click Done.

Now that you understand how to incorporate multiple transactions into a single package, you are ready to add connection objects to enable tasks to execute in parallel.

Add separate connections to the TransactionDemo package

In this procedure, you will add three new connection objects to the TransactionDemo package and configure each step in the package to use a different connection. You will then test the package s execution.

1. On the design sheet, right-click the FirstConnection to SBS_OLAP connection object, and then click Copy.

2. In a open area of the design sheet, click Paste.

3. Repeat the previous step two more times.

   This package now contains four connection objects.

4. Right-click an open area of the design sheet and click Disconnnected Edit.

5. In the console tree, expand Connections and click FirstConnection To SBS_OLAP Copy.

6. Double-click Name in the details pane, change the value to SecondConnection to SBS_OLAP , and then click OK.

7. In the console tree, click FirstConnection To SBS_OLAP Copy 2.

8. Double-click Name in the details pane, change the value to ThirdConnection to SBS_OLAP , and then click OK.

9. In the console tree, click FirstConnection To SBS_OLAP Copy 3.

10. Double-click Name in the details pane, change the value to FourthConnection to SBS_OLAP , and then click OK.

11. Click Close.

12. On the design sheet, double-click the Update CustomerStage Table step.

13. In the Existing Connection list, select SecondConnection To SBS_OLAP, and then click OK.

    

14. On the design sheet, double-click the Update ProductStage Table step.

15. In the Existing Connection list, select ThirdConnection to SBS_OLAP, and then click OK.

16. On the design sheet, double-click the Update SalesStage Table step.

17. In the Existing Connection list, select FourthConnection to SBS_OLAP, and then click OK.

    Tip  

    The renamed connection objects will not be displayed properly on the design sheet until you open and then close each of them to refresh their presentation on the design sheet.

18. On the toolbar, click Execute.

19. Click OK, verify that all four steps executed successfully, and then click Done.

Now that you have added separate connections to the TransactionDemo package, you are ready to change the task flow to parallel execution rather than sequential.

Change the task flow to parallel

In this procedure, you will configure the three update steps to execute in parallel within the second transaction. The Delete step will continue to execute in its own transaction.

1. On the design sheet, delete the On Success precedence constraint between the Update CustomerStage Table step and the Update ProductStage Table step.

2. On the design sheet, delete the On Success precedence constraint between the Update ProductStage Table step and the Update SalesStage Table step.

3. On the design sheet, click the Delete step, and then hold down the Ctrl key while you click the Update ProductStage Table step.

4. Right-click the Update ProductStage Table step, point to Workflow, and then click On Success.

5. On the design sheet, click the Delete step, and then hold down the Ctrl key while you click the Update SalesStage Table step.

6. Right-click the Update SalesStage Table step, point to Workflow, and then click On Success.

   

Now that you have configured the TransactionDemo package to execute tasks in parallel within the second transaction, you are ready to test its execution.

Test the execution of the TransactionDemo package with parallel steps

1. On the toolbar, click Execute.

   If more than one of these three Execute SQL tasks executes simultaneously , you will receive an error message. However, with these simple tasks, it is unlikely that you will receive an error message because they execute so quickly.

2. Click OK and then click Done.

3. On the design sheet, double-click the Update CustomerStage Table step.

4. Copy the INSERT statement, paste it into the SQL Statement box 100 times, and then click OK.

5. On the design sheet, double-click the Update ProductStage Table step.

6. Copy the INSERT statement, paste it into the SQL Statement box 100 times, and then click OK.

7. On the design sheet, double-click the Update SalesStage Table step.

8. Copy the INSERT statement, paste it into the SQL Statement box 100 times, and then click OK.

9. On the toolbar, click Execute.

10. Click OK to acknowledge that two tasks failed during execution. (On a very fast computer, only a single task might fail. You might also need to execute that task a second or third time to see the failure.)

    

11. Double-click one of the steps that failed to display the error.

    Each step failed because the ongoing transaction that the step attempted to join was being used by another step. A package cannot have more than one task within a transaction simultaneously making a connection to a SQL Server database.

    

12. Click OK, and then click Done.

Now that you have demonstrated the problem with multiple tasks connecting to SQL Server over separate connections within the same transaction, you are ready to solve this problem by having each simultaneously executing task execute on the main execution thread.

Enable the tasks executing in parallel to execute on the main thread

In this procedure, you will configure the Update CustomerStage Table , the Update ProductStage Table , and Update SalesStage Table steps to execute on the same thread.

1. On the design sheet, right-click the Update CustomerStage Table step, point to Workflow, and then click Workflow Properties.

2. On the Options tab, select the Execute On Main Package Thread check box and then click OK.

   

3. On the design sheet, right-click the Update ProductStage Table step, point to Workflow, and then click Workflow Properties.

4. On the Options tab, select the Execute On Main Package Thread check box and then click OK.

5. On the design sheet, right-click the Update SalesStage Table step, point to Workflow, and then click Workflow Properties.

6. On the Options tab, select the Execute On Main Package Thread check box and then click OK.

7. On the toolbar, click Execute.

8. Click OK, verify that all four steps executed successfully, and then click OK.

   All four tasks executed successfully because they executed on the same thread. This setting effectively serialized these tasks, negating the benefits of parallel execution.

9. On the Package menu, click Save As.

10. In the File Name box, change the save location to C:\Microsoft Press\SQL DTS SBS\Ch5\WorkingFolder\TransactionDemo , and then click OK.

11. Close the TransactionDemo package in DTS Designer.