Microsoft SQL Server 2000 Data Transformation Services- P6

Microsoft SQL Server 2000 Data Transformation Services- P6: 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- P6 DTS Connections and the Data Transformation Tasks226 PART II LISTING 8.2 Continued .ExceptionFileOptions = 1 ‘DTSExcepFile_SingleFile70 .ExceptionFileRowDelimiter = “{CR}{LF}” .ExceptionFileTextQualifier = “” .FetchBufferSize = 1 .FirstRow = 0 .InputGlobalVariableNames = “” .LastRow = 0 .MaximumErrorCount = 0 .ProgressRowCount = 1000 .SourceSQLStatement = “” End With pkg.Tasks.Add tsk ‘Create step for task Set stp = pkg.Steps.New With stp .Name = “stp” & sBaseName .Description = sBaseName .TaskName = tsk.Name End With pkg.Steps.Add stp fctCreateDataDrivenQueryTask = stp.Name Set conSource = Nothing Set conDest = Nothing Set tsk = Nothing Set cus = Nothing Set stp = Nothing ProcExit: Exit Function ProcErr: MsgBox Err.Number & “ - “ & Err.Description GoTo ProcExit End Function Conclusion The Data Driven Query task is a useful tool when you need to specify multiple results for a data transformation. The next chapter explains how to use multiple phases with the transforma- tion tasks.The Multiphase Data Pump CHAPTER 9 IN THIS CHAPTER • Enabling the Multiphase Data Pump 228 • Programmatic Flow with Multiple Phases 230 • Using the Phases 233 • Creating a COM Object with Visual C++ to Program the Phases 243 • Creating a Multiphase Data Pump in Code 243 DTS Connections and the Data Transformation Tasks228 PART II The multiphase data pump option allows you to write code at several different points in the data transformation process. You can use this option with the Transform Data task, the Data Driven Query task, and the Parallel Data Pump task. If you haven’t enabled the multiphase data pump, you can write code for only one point of the data transformation process—the point at which each row is being transformed. After enabling this option, you can write code for all these phases and subphases: • Pre Source Phase—Before the source query is executed. • Row Transform Phase—Each row of data is processed. • On Transform Failure—Subphase of the Post Row Transform phase. Occurs when there is an error in the transformation. • On Insert Failure—Subphase of the Post Row Transform phase. Occurs when a record fails to be inserted. • On Insert Success—Subphase of the Post Row Transform phase. Occurs when a record is successfully inserted. • Batch Complete Phase—A batch of records is successfully inserted. • Post Source Data Phase—The rows have all been processed. • Pump Complete Phase—The transformation task has completed its work. Enabling the Multiphase Data Pump The last section of this chapter explains how to create a multiphase data pump in code. In the DTS Designer, you can enable the multiphase data pump by doing the following: 1. Right-click on the Data Transformation Services node in the Enterprise Manager. 2. Select Properties from the pop-up menu. 3. Select Show multi-phase pump in DTS Designer on the Package Properties dialog, as shown in Figure 9.1. After selecting the multiphase option, you will see a Phases filter on the Transformation tab of the Transform Data Task Properties dialog, as shown in Figure 9.2. Each transformation can implement one or more of the phases. By selecting one of the phases, you can see which trans- formations have implemented a particular phase. The Phases tab of the ActiveX Script Transformation Properties dialog shows the eight phases and subphases where you can write code, as shown in Figure 9.3. You enable a phase by select- ing the check box beside it. You can set the default entrance function and create the default code for all the phases you have selected by clicking the Auto Gen button. The Multiphase Data Pump 229 CHAPTER 9FIGURE 9.1You select the multiphase data pump option in the Package Properties dialog. 9 ...