Using Data Transformation Services, part 4: Using the DTS Designer Objects (continued-2) - Using the Transform Task
(Page 6 of 10 )
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.
Next: Column Transformation Code: Step 1 >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
