Using Data Transformation Services, part 4: Using the DTS Designer Objects (continued-2) - Scenario of Data Transfer
(Page 2 of 10 )
In this tutorial we will be discussing mostly the ActiveX Script for the transformation. While scripting for DTS, a number of scripting language options exist. VBScript, JScript, Signed VBScript, Signed JavaScript, and PerlScript are the options on this computer. Only VBScript and Jscript are the default options, but since the computer has the other scripting languages installed these are automatically available for scripting in DTS.
In order to keep the discussion simple we assume that a text file is available to us which needs to be mapped into an MS Access table satisfying a set of requirements in the transfer. The next picture shows the text file that will be used to describe transformations using DTS.
This is a file with five fields with the filed "HireDate" showing time information as well as date and the FirstName and LastName are comma separated [equivalent of two separate columns]. The transformation we want is to transfer the concatenated FirstName and LastName into a column in an MS Access table; transfer the field "HireDate" to a column with the date transferred as YYYY-MM-DD; and transfer the "city" to "city", but all the letters in Upper case. We also drop the last column.
Next: Data Transfer Source >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
