collection with the
Add
method. The
PrecedenceBasis
property is set to the execution result,
and the
Value
property indicates that failure is the result that is to trigger the precedence.
L
ISTING
24.1
This ActiveX Script Creates a New On Failure Precedence Constraint
Option Explicit
Function Main()
Dim pkg, stpSource, stpDestination, prc
Set pkg = DTSGlobalVariables.Parent
Set stpSource = pkg.Steps(“stpBulkInsert”)
Set stpDestination = pkg.Steps(“stpInsertCustomer”)
Set prc = stpDestination.PrecedenceConstraints.New(stpSource.Name)
prc.PrecedenceBasis = DTSStepPrecedenceBasis_ExecResult
prc.Value = DTSStepExecResult_Failure
stpDestination.PrecedenceConstraints.Add prc
Main = DTSTaskExecResult_Success
End Function
DTS Packages and Steps
P
ART
V
476
If you create a precedence constraint with this code in an ActiveX Script task, the con-
straint will be in effect but will not be displayed in the Package Designer. In order for

Steps and Precedence Constraints
C
HAPTER
24
24
S
TEPS AND
P
ECEDENCE
C
ONSTRAINTS
477
The
ExecutionStatus
property of the
Step
object is listed as a read-only property in
the DTS Reference in Books Online. But there is a sample ActiveX script in Books
Online that sets the property to
DTSStepExecStat_Waiting
for the purpose of execut-
ing a task in a loop.
N
OTE
Chapter 16, “Writing Scripts for an ActiveX Script Task,” has an example of setting up a loop
in a DTS package.
Threads and Priority of Execution
DTS is a multithreaded application. Many tasks can be executed simultaneously, each one with
its own separate thread.
Package Level Thread Execution Parameters

DTSPackagePriorityClass_Low
1
DTSPackagePriorityClass_Normal
2
DTSPackagePriorityClass_High
3
The Maximum Number of Tasks Executed in Parallel
This setting limits the number of steps that are allowed to execute concurrently on separate
threads. The default value for this setting is four.
This setting can affect the performance of a DTS package. Raising this value can increase the
speed of a package’s execution, especially when multiple processors are available. More steps
can be executed simultaneously, as long as each step has had its precedence constraints satis-
fied. But if this value is raised too high, package execution can be slowed because of excessive
switching between threads.
In code, this setting is the
MaxConcurrentSteps
property of the
Package
object.
Step Level Thread Execution Parameters
There are six settings in the Execution group on the Options tab of the Workflow Properties
dialog:
DTS Packages and Steps
P
ART
V
478
30 0672320118 CH24 11/13/00 4:55 PM Page 478
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
•Task priority

DTSStepRelativePriority_AboveNormal
4
DTSStepRelativePriority_Highest
5
Execute on Main Package Thread
DTS normally spawns separate threads to execute different steps of the package. This setting
changes that behavior for one particular step by forcing it to be executed on the main package
thread.
These are the situations where it is necessary to execute a process on the main package thread:
• If the data provider is not free-threaded and does not support parallel execution of tasks.
This is true for the Microsoft Jet OLE DB Provider, as well as the providers for Excel,
dBase, Paradox, and HTML files. If more than one task is being executed with one of
these providers at the same time, they should all be executed on the main package
thread.
• If you are using custom tasks that have been created with Visual Basic.
• If you are executing a package from Visual Basic.
• If you want to debug multiple ActiveX Scripts with the script debugger provided with
Microsoft Visual InterDev 6.0 or the Microsoft Windows NT 4.0 Option Pack.
Steps and Precedence Constraints
C
HAPTER
24
24
S
TEPS AND
P
ECEDENCE
C
ONSTRAINTS
479

FALSE
.
DSO Rowset Provider
This option allows a DTS package to return a recordset.
This option is the
IsPackageDSORowset
property of the
Step
object, a boolean value with a
default value of
FALSE
. It is discussed in Chapter 23, “The DTS Package and Its Properties.”
Disable This Step
When you choose this option, you block the execution of this step when the package is exe-
cuted. As discussed in the section on precedence constraints, you can specify another task to
DTS Packages and Steps
P
ART
V
480
Errors can be generated if several tasks are being executed simultaneously using data
sources or custom tasks that do not support parallel execution. Use the Execute on
main thread option to avoid those problems.
C
AUTION
30 0672320118 CH24 11/13/00 4:55 PM Page 480
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
run if, and only if, a particular task is disabled. You do this by using the
DTSStepExecStat_
Inactive

S
TEPS AND
P
ECEDENCE
C
ONSTRAINTS
481
The package has a corresponding property called
FailOnError
. It is set on the
Logging tab of the Package Properties dialog and is discussed in Chapter 23. If the
package property
FailOnError
is set to
True
, the first error in any step will cause the
package to terminate with failure. The step property
FailPackageOnError
causes the
package to fail only if that particular step fails.
If both
FailOnError
and
FailPackageOnError
are set to
FALSE
(their default settings),
all the steps in the package can fail and the package will still complete successfully.
N
OTE

UseTransaction
property of the
Package
object. It is a boolean property
with a default value of
True
.
Commit On Successful Package Completion
If this option is selected and a transaction is in effect, that transaction will be committed auto-
matically when the execution of the package is completed.
If this option is set to
FALSE
,a transaction that is in progress when the package completes its
execution will be rolled back.
The
AutoCommitTransaction
property of the
Package
object sets this option. This is a boolean
property with a default of
True
.
30 0672320118 CH24 11/13/00 4:55 PM Page 482
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Transaction Isolation Level
The transaction isolation level can be set to one of five levels in the DTS Designer. These five
levels are assigned using eight constants for the corresponding property, the
TransactionIsolationLevel
property of the
Package

•Value: 4096
• ANSI SQL-92 Isolation Level 2
• Equivalent to constant
DTSIsoLevel_CursorStability
• This is the default transaction isolation level in SQL Server and in DTS.
Steps and Precedence Constraints
C
HAPTER
24
24
S
TEPS AND
P
ECEDENCE
C
ONSTRAINTS
483
30 0672320118 CH24 11/13/00 4:55 PM Page 483
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
• Does not allow dirty reads.
•Allows non-repeatable reads and phantom reads.
•You are not allowed to read data modifications that have not been committed.
DTSIsoLevel_RepeatableRead
Equivalent to Transact-
SQL SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
.
•Value: 65536
• ANSI SQL-92 Isolation Level 3
• Does not allow dirty reads or non-repeatable reads.
•Allows phantom reads.

request to participate in a connection will be ignored.
There can be only one transaction active in a package at a time. If one is currently active, this
step will join it. If a transaction is not active, this step will start a new one.
In code, this option is implemented as the
JoinTransactionIfPresent
property of the
Step
object. This is a boolean property with a default value of
FALSE
.
DTS Packages and Steps
P
ART
V
484
30 0672320118 CH24 11/13/00 4:55 PM Page 484
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
F
IGURE
24.6
You have three choices regarding a step’s involvement with a transaction.
Commit Transaction on Successful Completion of This Step
If this option is selected, the current transaction will be committed if this step completes suc-
cessfully. All the data modifications made in this step, and in previous steps included in the
transaction, will be committed.
You can select this option without choosing to have the step join the transaction.
After this step is completed and the transaction is committed, the next step that is set to join a
transaction will start a new transaction.
This option is the
CommitSuccess

will start a new transaction.
If this option is not selected, this step is included in a transaction, and the step fails, the trans-
action will continue without being committed or rolled back.
This option is the
RollbackFailure
property of the
Step
object.
Participation in Transactions by Connections and Tasks
Data connections and tasks have to be able to participate in distributed transactions, or else
they are not allowed to join DTS transactions.
You can commit or roll back a DTS transaction based on the success or failure of any task. The
task does not have to participate in the transaction for it to trigger the commit or the rollback.
Here are the tasks that can participate in DTS transactions:
•Transform Data
• Data Driven Query
•Execute SQL
• Bulk Insert
• Message Queue
•Execute Package
The following tasks cannot participate in a DTS transaction:
• Copy SQL Server Objects
•File Transfer Protocol
• Dynamic Properties
• ActiveX Script
•Execute Process
• Send Mail
If you select Join transaction if present for a step associated with one of these tasks, an error
will be generated at runtime and the task and package will fail.
DTS Packages and Steps

Steps and Precedence Constraints
C
HAPTER
24
24
S
TEPS AND
P
ECEDENCE
C
ONSTRAINTS
487
F
IGURE
24.7
The easiest transactions have steps that execute sequentially.
30 0672320118 CH24 11/13/00 4:55 PM Page 487
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Here’s what you have to do to set up this simple transaction:
1. Select Use transactions and Commit on successful package completion on the Advanced
tab of the DTS Package Properties dialog. These are the default choices.
2. Select Join transaction if present and Rollback on failure on the Options tab of the
Workflow Properties dialog for all of the steps. Do not select Commit transaction on suc-
cessful completion of this step for any of the steps.
3. Set precedence constraints so that the tasks are executed sequentially. Use On Success
precedence.
As soon as an error occurs in this package, all the data modifications that have been previously
made will be rolled back. The On Success precedence constraints will prevent any more steps
from being executed.
You can set all the properties for this transaction programmatically by executing the script in

prc.Value = DTSStepExecResult_Success
Next
End Select
Next
Main = DTSTaskExecResult_Success
End Function
If you have some tasks that you want to run after the transaction, you can use the VBScript
code in Listing 24.3 in a Workflow ActiveX script. The code checks if any of the steps in the
transaction have failed or if all the steps in the transaction have been executed. The task is exe-
cuted when one of these conditions is met.
L
ISTING
24.3
A Workflow ActiveX Script That Can Be Used to Watch for the Completion
of a Transaction
Option Explicit
Function Main()
Dim pkg, stp
Set pkg = DTSGlobalVariables.Parent
Main = DTSStepScriptResult_ExecuteTask
For Each stp In pkg.Steps
If stp.JoinTransactionIfPresent = True Then
If stp.ExecutionResult = DTSStepExecResult_Failure And _
stp.ExecutionStatus = DTSStepExecStat_Completed Then
Main = DTSStepScriptResult_ExecuteTask
Exit For
End If
If stp.ExecutionStatus = DTSStepExecStat_Waiting Then
Main = DTSStepScriptResult_RetryLater
Steps and Precedence Constraints

Main = DTSStepScriptResult_DontExecuteTask
End If
End Function
A Transaction with Steps Executed in Parallel
Sometimes, for better performance, you may want to execute several steps in a transaction in
parallel. Figure 24.8 shows a package with steps executing in parallel. The DTS package with
this transaction is on the CD in a file called ParallelTransaction.dts.
If you use parallel execution in a transaction, you have to be aware of these issues:
• If you have two connections to the same instance of SQL Server 2000, one of them is
being used, and a task using the second is set to join the transaction, the package will
fail. One way to avoid this error is to use a single connection for each SQL Server 2000
that is accessed by your package. Only one task can use a connection at a time, so the
tasks will actually be executed serially even if they’re set to execute in parallel. If there
are two connections to separate instances of SQL Server 2000, they can be successfully
executed in parallel.
• It can be hard to prevent new transactions from starting after a transaction failure. For
example, in Figure 24.8 a transaction could start and be joined by the first tasks on each
of the three branches of the programmatic flow. If From Pubs fails and the other two
steps are completed successfully, the data modifications made in all three steps will be
rolled back. The problem is that a new transaction will be started with the Report
Employees tasks. To avoid this problem, you can select Fail package on first error on the
Logging tab of the Package Properties dialog.
DTS Packages and Steps
P
ART
V
490
L
ISTING
24.3

ECEDENCE
C
ONSTRAINTS
491
30 0672320118 CH24 11/13/00 4:55 PM Page 491
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
F
IGURE
24.9
You can use an ActiveX Script task to commit or roll back a transaction executed in parallel.
5. Set all the precedence constraints to execute on completion.
6. The script for the ActiveX Script task is shown in Listing 24.4. This script assumes there
is only one transaction in the package. If there were more than one, you would have to
reference each step in the transaction explicitly, rather than looping through all the steps
and examining all that were participating in a transaction.
L
ISTING
24.4
Code for an ActiveX Script Task to Commit or Roll Back a Transaction
Option Explicit
Function Main()
Dim pkg, stp
Set pkg = DTSGlobalVariables.Parent
Main = DTSTaskExecResult_Success
For Each stp In pkg.Steps
If stp.JoinTransactionIfPresent = True Then
If stp.ExecutionResult = DTSStepExecResult_Failure Then
Main = DTSTaskExecResult_Failure
DTS Packages and Steps
P

Package task in the parent package, and the child package terminates successfully, the
whole transaction is committed.
•You can call another package from a child package that is participating in a transaction
so that steps in additional packages are included in the transaction.
Steps and Precedence Constraints
C
HAPTER
24
24
S
TEPS AND
P
ECEDENCE
C
ONSTRAINTS
493
L
ISTING
24.4
Continued
30 0672320118 CH24 11/13/00 4:55 PM Page 493
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
If you execute a package using the OLE Automation stored procedures in an Execute SQL
task, using DTSRun in an Execute Process task, or using COM in an ActiveX Script task, you
cannot include the called package in the DTS transaction.
Workflow ActiveX Scripts
A Workflow ActiveX Script is run at the beginning of a step’s execution, before the task asso-
ciated with that step is executed. The main purpose of the Workflow ActiveX Script is to deter-
mine whether or not the task should be executed.
You choose a Workflow ActiveX Script by selecting the Use ActiveX script box on the bottom

• This is the default return value for a workflow script. It is also the return value when the
Auto Gen. (auto generate) button is clicked.
DTSStepScriptResult_DontExecuteTask
Does not execute the task.
•Value 1
• The task associated with this step is not executed during the execution of the package.
DTSStepScriptResult_RetryLater
Retries the task later.
•Value 2
• The task associated with this step is not executed when the workflow script is completed.
The execution method of the task is called again later in the execution of the package.
When the step is retried, the workflow script is again executed before the task.
Steps and Precedence Constraints
C
HAPTER
24
24
S
TEPS AND
P
ECEDENCE
C
ONSTRAINTS
495
30 0672320118 CH24 11/13/00 4:55 PM Page 495
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.