SQL Server 2000 DTS Part 12 - Transactional Support
March 26, 2004
In the concluding article of this series, we will discuss transaction support in SQL Server 2000 Data Transformation Services. Transactions within DTS packages serve the same purpose as in other SQL Server based operations - they provide the set of features described collectively using the term ACID, which is an acronym derived from four transactional characteristics - atomicity, consistency, isolation and durability. Atomicity means that a particular unit of work is either fully completed or not performed at all, consistency refers to the state of the underlying data, isolation specifies that each unit of work executes independently of others, and durability reflects permanent status of data changes.
By default, each task performs its operations independently of others within the same DTS package (from the transactional point of view). If an error is encountered, execution of the current task is abandoned. While you can use settings in the Error handling section on the Logging tab of the Package properties dialog box ("Fail package on the first error") to specify whether the entire package will fail at this point or control execution of subsequent tasks with workflow elements (which determine whether the next task will execute on success, failure, or completion), neither one of these two methods has any influence on operations completed prior to the failure. In order to accomplish this, you need to configure all related tasks to participate in the same transaction. This way, you can ensure that either all of them will complete successfully or will roll back to the original state if any one fails.
At the same time, however, you need to be aware that a package can execute only a single transaction at a time and that not every package is capable of properly handling transactions. The limitations are imposed by the types of connections and tasks employed within a package:
Since DTS relies on the Microsoft Distributed Transaction Coordinator (MS DTC) when processing its transactions, you should ensure that the MS DTC service is running before you launch a package. In addition, you need to consider several configuration options that affect transactional behavior of a package and its tasks (tasks represent units of execution and are frequently referred to as steps, when discussing their role in DTS-based transactions):
Now let's look at a very simple example illustrating the use of transactions in DTS packages. We will create two tasks, configure them as part of the same transaction and examine the results. As our data stores, we will use a text file containing a single entry:
and the Shippers table in the Northwind database. Start by creating the text file (e.g. C:\Temp\Shippers.txt) and copying the line listed above into it. Next, launch DTS Designer and insert a Microsoft OLE DB Provider for SQL Server Connection pointing to the Northwind database into a new package. From the Task menu (or the toolbar on the left hand side) select Bulk Insert Task and configure its properties by typing a description (e.g. BULK INSERT Shippers), selecting the existing Northwind connection from the Existing connection listbox, pointing to Shippers as the destination table, and specifying C:\Temp\Shippers.txt as your source data file). Ensure that a semicolon is used as column delimiter. Close the Bulk Insert Task Properties dialog box, right click on the task icon, and select Workflow -> Workflow Properties context sensitive menu option. In the Workflow properties dialog box, switch to the Options tab and select the "Join transaction if present" check box. This will trigger the creation of a new transaction when the task is launched. Close the dialog box. Create the second task by selecting Execute SQL Task from the Task menu. In the Task Properties dialog box, type in a Description (e.g. "INSERT ShipperID 1"), select Northwind in the Existing connection listbox, and enter the following SQL Statement:
SET IDENTITY_INSERT Northwind.dbo.Shippers ON GO INSERT INTO Shippers (ShipperID, CompanyName, Phone) VALUES (1, 'Road Runner', '(503) 555-4567') GO SET IDENTITY_INSERT Northwind.dbo.Shippers OFF GO
This statement will attempt to insert a duplicate row into the Shippers table, which, of course, will result in failure. Since we are planning to run both tasks as part of the same transaction, the bulk insert changes will be rolled back and the content of the Shippers table will remain the same as prior to package execution. In order to complete our configuration, bring up the Workflow Properties dialog box for the Execute SQL task, set the "Join transaction if present" and "Rollback transaction on failure" checkboxes, and confirm the configuration by clicking on the OK command button. From the DTS Designer interface, insert the workflow "On Success" element between the Bulk Insert and Execute SQL tasks. Save and execute the package. In the Executing DTS Package dialog box, you should be able to see the progress of the execution, indicating success of the first task and failure of the second. Finally, check the content of Shippers table and verify that it has not changed.
You can experiment with additional configuration options (e.g. by setting "Commit transaction on successful completion of this step" for the first task) to further explore the transactional capabilities built into DTS. You can also refer to Books On Line for discussion on more elaborate package designs, such as the checkpointing package transactions, inherited transactions or parallel execution.
Even though this article concludes our introductory overview of SQL Server 2000 Data Transformation Services, we will revisit this subject when discussing DTS Security in our upcoming SQL Server 2000 Security series.