Using Data Transformation Services, part 4: Using the DTS Designer Objects (continued-2)
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:
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.
In the DTS editor drag and drop a source type text file connection, which will pop up a Connection properties window as shown.
Using the ellipsis browse to the file described earlier called, ClientInfo.txt. This is the file that will be used as the source whose content was shown in a previous picture. When you click OK to this window you will be required to look at the file properties before further proceeding as shown in this Text File Properties window,
since the first row shown in the preview area has the column names it will be necessary to check the 'First row has column names' check box. Clicking on Next will take you to the next window as shown in this picture. Since the original data was comma separated text this window automatically picked up this information. If needed this may be changed to a different separator type.
You click Finish and then OK to the ensuing window that will complete the configuring of the Text file connection. In the DTS editor you will be seeing the icon for the Text file object.
Create an MS Access database application (that is an *.mdb file) in a suitable location. In this tutorial it is named as DTSClient.mdb and located in the C: directory. Drag and drop an MS Access connection to the design pane of the DTS Editor and using the ellipsis button locate the above MS Access file. The configured MS Access connection is as shown here. Clicking OK should complete this process. Sometimes the correct authentication information may be needed.
From the Task Collection click on the Data Transform Task and place it on the Text file first and then place it on the MS Access icon next. A connecting link will be developed. The second tutorial illustrates this. The design pane now has the source and the destination with the connecting, data transform task as shown in this picture.
Place the cursor on the vertical connecting line and the cursor changes to a double headed East<->West cursor type. Now right click this Data Transform task to reveal its properties window as shown in this picture.
The window opens with the default tab, Source, namely, the text file in this case.
A meaningful description may be typed in. In this tutorial the default is accepted. The Table/View is the only option for this connection. It is possible to have a preview of this source by clicking the Preview... button. If you click OK to this you will be faced with a Package error since all information for the transform has not been specified at this point.
Click on the next tab, Destination. This brings up this next, Create Table window. It already has the name of the text file name as default table name. Although changes can be made to the table properties, defaults are accepted by clicking OK since our focus is on transformation of columns. The destination information now appears as shown in this picture.
Now we come to the next tab, the Transformations tab. Click on the Transformations tab, and this brings up this screen. This is where transformations are to be defined. When it pops up for the first time, column to column mapping lines are in place although no transformation in particular is selected as shown in this picture. For five columns five transformations are in place.
Col001 and Col002 of the source has the FirstName and LastName and this should be transferred to the Col001 of destination. Hence we remove the existing mapping by clicking on the connecting link and use the Delete button. The window should look like in the next picture.
Now highlight Col001 and click on the button New. This brings up the list of transformations that are available for the operations.
From this the ActiveX Script will be chosen. By highlighting ActiveX Script and clicking OK brings up the next window where we need to add the script that will concatenate columns 1 and 2 from the source into column 1 of destination table.
When you click OK to this you will see the following window Transformations Options in display. Take a minute or two to read the Transformation Description.
You need to identify the source columns and the destination columns that participate in this transformation. Click on the tab Source Columns. This will bring up the next window.
You will see the Available Columns in the Source. But since the default is copy columns, only Col001 is shown in the Selected Columns. Highlight Col002 in the Available Columns and click the > button to send it to the Selected columns. Click the Destination Columns tab after sending the Col002 to the Selected Columns. This brings up the next window wherein you configure the destination columns. To start with no columns are selected, the picture shows that out of the available columns, Col001 is selected(by the user).
Now click on the General tab. We will be returned to the one we saw earlier.
Now click on the Properties... button. This brings up the next screen, ActiveX Script Transformation Properties window, wherein you need to add your scripting for the transformation.
Here you can choose the scripting language by clicking on the drop-down along side the label marked Language:. In this tutorial the default is accepted, which is VB Script. Click on browser to encounter the next window and look at the Package details, expand the source and destination columns to verify your choices. Everything related to this package ie shown in this expandable/collapsible treeview control.
Now click on the Language tab. Directly below the language dropdown you have access to the various functions, control flow statements, etc which can be clicked, to be added to the scripting area on its right. It is best to use this rather than typing in the functions/control flow code. The window also opens up with a default function Main(). If nothing is added this function always returns a success.
What is needed is to place the concatenated Col001 and Col002 from source in Col001 of destination. The source column variable is DTS Source("Col001") and therefore a simple code like this should suffice.
With this script added (typed-in) the screen should appear as shown here.
If you click on the button Parse, if the parsing passes the test you should see this:
Just because the parsing is OK does not mean that there will be processing errors, since the provider should be able handle the code. Now click on the button Test....Now the task is executed, but the result goes into a temporary table and will not apply the changes to your original configuration.
The Package Execution Results came from the Main function since the package executed without errors. Clicking away the message box brings the Testing Transformation to the front and you may view the result of this transformation in the following View Data screen as shown. You click on OK and then Done to return to the main screen.
So the transformation task has been accomplished. You may save the code for this transformation by clicking on the button Save. The script is saved to a *.bas file on your preferred location with a user supplied name.
Close the windows to return to the Transformation Data Task Properties window which should show the mappings you just carried out as shown. If you want you may test the transformation again. Also you are at liberty to edit or even delete the transformation.
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:
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:
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 TaskProperties window as shown. In preparation for the next step, the mapping of column Col004 has been deleted in this picture.
In the finals step we shall capitalize all the characters in the 'City' field. This can be accomplished using the built-in transformation, Uppercase String mentioned in the list of transformations earlier. Highlighting Col004 (this will be mapped to Col003 of the destination), click on New and choose the Uppercase String from the Create New Transformation window. This takes you to the Transformation Options window where you choose the source column as Col004, the destination column as Col003 and then click the General tab and then click on Properties of the transformation. The following window appears as shown.
Click OK to all and close the screens to comeback to the Transform Data Task Properties window. You may want to test this transformation in this screen to see the View Data window as shown.
Finally we shall delete the PostalCode field by simply deleting this link. The final screen should appear as follows.
The package is saved which may be password protected and saved with a user supplied name, in this case Trnsfrm. In the previous tutorials executing a package has been discussed. It can be done in several ways. From the Package menu item in the DTS editor click on Execute. You should see the successful execution message. Now you should be able to open up the MS Access database and look for the table. Presto!, here it is in the next picture with the table opened in data View mode.
Summary
The DTS designer together with the built in transformations as well as the ActiveX scripting support makes the data transfer a piece of cake. Some simple scripts were shown but in real production scenarios the code may get more complicated. When choosing the "Create Table" in MS Access we used the defaults that made the table appear as shown above. We could have made all the changes to the column names, etc which we did not. This tutorial only scratched at the surface of this tool, and the reader is advised to look for other references as well as get a firm grip on VB script scripting, especially with regard to string manipulation.
MS SQL Server 
Using Data Transformation Services - Part 1
