Using Data Transformation Services, part 4: Using the DTS Designer Objects (continued-2)
(Page 1 of 10 )
In the newest tutorial of the DTS series, Jay reviews the subjects of his last tutorials and moves on to a new proceedure. He focusses on discussing using ActiveX Script for transforming data as it's moving from the source to the destination.
In the first tutorial on DTS the creation of the basic DTS unit, a package, is described using the Import Export wizard. Immediate execution of the package initiates and completes the process as well as creating a package for future reuse. A concrete step-by-step process of copying a database from one SQL server to another SQL server edition using the DTS editor is also described. Copy database is one of the simplest of tasks that can be easily accomplished with the Import Export Wizard or the DTS Editor.
In the second tutorial, the main focus is on the usage of collections: connections and tasks. In order to describe their usage, a scenario is chosen where an MS Access table is populated by a query on three of the related tables in an MS SQL 2000 server which is then transferred to an MS Excel Worksheet after further filtering. In this example the entire package is developed using the DTS Editor.
In the third tutorial, queried data from an MS SQL Server is transferred to a Text file. Since DTS differentiates between destination type text files and source type text files an intermediate ActiveX Script task is used to copy this destination type text file to a source type text file. This file is further transferred to a second destination type text file after further processing. In order to stage these tasks, a workflow process is installed so that only after the first data transfer, the second data transfer can take place. Data Transformation task requires the existence of source and destination at design time.
DTS uses native OLEDB provider for its connectivity, but suitable configured ODBC counterparts can also be used. In this case DTS uses the OLEDB provider for ODBC for connectivity. The Data Pump is central to the operation for actually pumping the data from source to destination. Just bringing data from source to destination would not have been of much value as other processes such as Copy and FTP could do the same thing. The ability to transform data on its way to the destination is what makes it a unique tool.
Transformation in DTS occurs between source column to destination column on a row-per-row basis. This transformation can be quite complex, or can be very simple. The default transformation between source to destination is just copying row-by-row of data which is the most common type of transformation used in practice. ActiveX script is used for more complex types of transformations such as combining source columns to feed into a single destination column, aggregation, validation etc. Whatever the script can accomplish, the DTS will be able to use in the transformation. However, if a new transformation is called for, a number of options exist as shown in the following list:
- ActiveX Script
- Copy Column
- DateTime String
- Lowercase String
- Middle of String
- Read File
- Trim String
- Uppercase String
- Write File
Next: Scenario of Data Transfer >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
