SQL Server Solutions - Defining Transformations

On the Transformations tab, you'll see by the arrows between column names that DTS has taken a stab at guessing which source columns should map to which destination columns. Its guesses are fairly close, but there are a few problems with them. For one thing, the UserID column in the UserAccounts table is an identity column, so we don't want to copy values into this column; rather, we should leave this up to SQL Server. For another, we'd like the FullName column to be a concatenation of the FirstName and LastName columns from the Employees source table. Also, the Password column in the destination table should simply be a constant value, the same for all rows in the table.

Click the Delete All button to remove all the default mappings so we can create our own. Next comes the more interesting part, where you can see the true flexibility of DTS transformations. Select the LastName, FirstName, and Title columns in the Source list and the UserName, FullName, Description and Password columns in the Destination list box. Click the New button to add a transformation, and then choose ActiveX Script in the Create New Transformation dialog box, and click OK.

Then, in the Transformation Options dialog box, click the Properties button on the General tab. (The other two tabs, Source Columns and Destination Columns, simply show the columns we've already selected.) You should now be looking at the ActiveX Script Transformation Properties dialog box. You'll notice that DTS has written some default column mapping script for us, but we need to make some changes. Change the code listing to match that shown in Listing A. The ActiveX Script Transformation Properties dialog box will appear, Click OK through each of the preceding dialog boxes until you--re looking at the DTS package designer window again.

Listing A: A fairly simple ActiveX Script transform


' Visual Basic Transformation Script



' Copy each source column to the destination column

Function Main()

   DTSDestination("UserName") = DTSSource("LastName")

   DTSDestination("FullName") = DTSSource("FirstName") & _ " " & DTSSource("LastName")

   DTSDestination("Description") = DTSSource("Title")

   DTSDestination("Password") = "password"

   Main = DTSTransformStat_OK

End Function

Go back