Using Data Transformation Services, part 4: Using the DTS Designer Objects (continued-2) - Column Transformation Code: Step 2
(Page 8 of 10 )
In this case we shall take the date column and clean up the formatting so that when it gets transferred the date will be in a simpler format (yep, your manager wanted this way!). The details follow along the same lines and we shall jump right to the ActiveX Script Transformation Properties window after doing the essentials of identifying the column, deleting the existing mapping, calling for a new mapping that brings up the ActiveX Script Transformation window. This shown in the next picture. The date format in the source, is of the form,
YYYY-MM-DD 00:00:00.
This needs to be changed to YYYY-MM-DD format. It was mentioned that you may add commands, control flow statements by clicking on the keyword from the left into the scripting text area box. If you click on, say MsgBox you will see the following added to the scripting area:
MsgBox( <<prompt>> , <<buttons>> , <<title>> , <<helpfile>> , <<context>> )
This is a helpful syntax that you should use although it has these weird angle brackets. Now in order to extract the first ten characters from this you could use the VB Script's LEFT() function. The following statement would convert the format to the desired format:
DTSDestination("Col002") =Left(DTSSource("Col003") , 10 )
This is shown in the next picture.
The result of this transformation in the View data window is as shown in the next picture. Your manager may want to see 05/01/1992 instead of 1992-05-01, but then, you have the full scripting power of VB script to make this change in code.
Close all screens to return to the Transform Data Task Properties window as shown. In preparation for the next step, the mapping of column Col004 has been deleted in this picture.
Next: Column Transformation Code: Step 3 >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
