Microsoft SQL Server 2000 Data Transformation Services- P11

Microsoft SQL Server 2000 Data Transformation Services- P11: Data Transformation Services in Microsoft SQL Server 2000 is a powerful tool for movingdata. It’s easy to use, especially when you’re creating transformation packages with the DTSWizard. But it’s also a flexible tool that you can customize to gain a high degree of controlover the transformation of your data.
Nội dung trích xuất từ tài liệu:
Microsoft SQL Server 2000 Data Transformation Services- P11 DTS Packages and Steps476 PART V 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. LISTING 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 NOTE 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 it to be displayed, you have to open the Workflow Properties dialog for the step and click OK. The Execution Status of the Step Steps execute when all of their precedence constraints have been met and the step has a status of DTSStepExecStat_Waiting. If the step has any other status, it will not be executed. You can change the execution of a step by setting the ExecutionStatus property of the Step object in code: Steps and Precedence Constraints 477 CHAPTER 24 • If a step has already been executed and you then set the ExecutionStatus property to DTSStepExecStat_Waiting, the step will be executed again. • If a step is waiting to be executed and you set the ExecutionStatus to any of the con- stants listed in Table 24.2 besides DTSStepExecStat_Waiting, the step will not be exe- cuted. The status that makes the most sense to use for this purpose is DTSStepExecStat_Inactive. NOTE 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.Chapter 16, “Writing Scripts for an ActiveX Script Task,” has an example of setting up a loopin a DTS package.Threads and Priority of ExecutionDTS is a multithreaded application. Many tasks can be executed simultaneously, each one withits own separate thread.Package Level Thread Execution ParametersThere are two thread execution properties for the DTS package as a whole—Priority class andLimit the maximum number of tasks executed in parallel. These properties can be set on the 24General tab of the DTS Package Properties dialog, as shown in Figure 24.4. You can open thisdialog by choosing Properties on the Package menu when no objects are currently selected in CONSTRAINTS PECEDENCE STEPS ANDthe Design Sheet.The Priority ClassThis setting determines the Microsoft Win32 process priority class for the DTS package whenit is executed. The possible values are low, normal, and high.In the object model, this setting is the PackagePriorityClass property of the Package object.The three allowed settings for this property are displayed in Table 24.3. DTS Packages and Steps478 PART V FIGURE 24.4 The priority class and the maximum number of tasks executed in parallel are the two thread execution parameters that can be set for the DTS Package as a whole. TABLE 24.3 Constants Used for the PackagePriorityClass of the Package Object Constant Value 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: Steps and Precedence Constraints 479 CHAPTER 24 • Task priority • Execute on main package thread • Close connection on completion • DSO rowset provider • Disable this step • Fail ...