Microsoft SQL Server 2000 Data Transformation Services- P13: Data Transformation Services in Microsoft SQL Server 2000 is a powerful tool for moving data. It’s easy to use, especially when you’re creating transformation packages with the DTS Wizard. But it’s also a flexible tool that you can customize to gain a high degree of control over the transformation of your data. | DTS Packages and Steps 576 Part V Figure Column names versus column ordinal numbers in scripts in Transform Data tasks. There are two problems with switching from column names to column ordinal numbers The code is harder to read and write. The ordinal numbers do not consistently identify the columns. All the ordinal numbers of the columns are changed whenever you view the Source Columns tab or the Destination Columns tab of the Transformation Options dialog. Listing shows VBScript code for an ActiveX Script task that will dynamically modify all the ActiveX Script transformations in all the Transform Data tasks in the package replacing the column names with the column ordinal numbers. This code is included on the CD in a package stored in the file. This package also has a task that switches all the ordinal numbers back to names which can be run at the end of the package execution. Listing VBScript Code That Switches Column Names to Column Ordinal Numbers for All Transform Data Tasks in a DTS Package Option Explicit Function Main High-Performance DTS Packages 577 Chapter 28 Listing Continued Dim pkg tsk cus trn col Dim sScript sFind sReplace Set pkg For Each tsk in Select Case Case DTSDataPumpTask DTSDataPumpTask2 Set cus For Each trn in Select Case Case _ sScript Text .Value For Each col in sFind DTSDestination . . sReplace DTSDestination CStr sScript Replace sScript sFind sReplace Next 28 High For Each col in sFind DTSSource . . sReplace DTSSource CStr sScript Replace sScript sFind sReplace Next Text .Value sScript End Select Next End Select Next Main DTSTaskExecResult_Success End Function DTS .
