SQL Server 2000 DTS Part 8 - DTS Designer Tasks - Transform Data Task - Page 2

Typically, it is desirable from an efficiency perspective to limit the number of transformations. In our case, this can easily be accomplished by removing the three automatically created Copy Column transformations and replacing them with one, which includes all columns. To implement this, at the bottom of the dialog box click first on the Select All button and then on Delete All. At this point, you will notice that three arrows have been removed. Clicking on the New button will display the Create New Transformation dialog box. From there, choose the Copy Column option and click on OK. This will trigger the display of the Transformation Options dialog box. If you click on the Properties button, you will notice that this single transformation contains the mapping between all three columns in the source and destination tables. Once you click on OK, you will see the graphical representation of the transformation reflecting this new configuration. Use the Test button to test the outcome - you should receive confirmation that the package was executed successfully (you will also be able to view the results).

Now, let's see how we can accomplish the same result with a different approach using ActiveX Script. From the Transform Data Task Properties, use the Delete button to remove any existing transformations. Next, click on the Select All button and ensure that all columns in the Source and Destination are selected. Display the Create New Transformation dialog box with the New button and select the ActiveX Script entry. Click on OK. This will present you with the Transformation Options dialog box. Check its properties (using the Properties button). You will find that our steps resulted in the automatic creation of the following script:

Function Main()
	DTSDestination("ShipperID") = DTSSource("ShipperID")
	DTSDestination("CompanyName") = DTSSource("CompanyName")
	DTSDestination("Phone") = DTSSource("Phone")
	Main = DTSTransformStat_OK
End Function

As before, you can test it and you should receive confirmation of its successful execution. This shows you that you can use different transformations to arrive at the same result. While Copy Column method is faster, ActiveX Script offers more flexibility and can be used with lookups.

Lookups, configurable from the Lookups tab, allow you to retrieve a value or set of values via a DTS connection. If you intend to access the same database as Transform Data Task, you can use existing source or destination connections, however, you should keep in mind that a separate lookup connection offers the best performance. Each lookup entry consists of its unique name, the connection name, cache setting (number indicating how many lookup results are cached), and SQL Query that defines the lookup operation. The query is defined with DTS Query Designer and includes one or more parameters (designated by question marks and replaced with values derived from a data source or DTS global variables, when transformation is executed). More specifically, this is typically accomplished with the following ActiveX Script code:

DTSDestination("DestinationColumn") = DTSLookups("LookupQuery").Execute("ParameterList")

For example, let's imagine that we want to create a table at the destination that will contain, in addition to the information in the Shippers table, the total number of orders shipped by each company. This can be done with the following query:

SELECT	COUNT(OrderID) AS Expr1
FROM	Orders
WHERE	(ShipVia = ?)

Create this query on the Lookups tab and name it GetOrders. Next, alter the entry on the Destination table tab by creating a new table in tempdb called ShippersOrders using the following SQL Statement:

CREATE TABLE [ShippersOrders] (
[ShipperID] int NOT NULL, 
[CompanyName] nvarchar (40) NOT NULL, 
[Phone] nvarchar (24) NULL,
[Orders] int)

When you switch to the Transformations tab, you will notice that the Orders column appears in the destination table but it is not affected by the existing transformation. To change this, click first on Edit, then on the Properties button (in the Transformation Options dialog box). Finally, in the code section of ActiveX Script Transformation Properties dialog box, alter the code, so it looks like the following:

Function Main()
	DTSDestination("ShipperID") = DTSSource("ShipperID")
	DTSDestination("CompanyName") = DTSSource("CompanyName")
	DTSDestination("Phone") = DTSSource("Phone")
	DTSDestination("Orders") = DTSLookups("GetOrders").Execute(DTSSource("ShipperID"))
	Main = DTSTransformStat_OK
End Function

Executing this task will populate the extra column with values derived from the Orders table using the ShipperID column for each row in the source table. You should note, however, that in general it is better to avoid lookup queries for performance reasons and look for alternative solutions. For example, in our case, the same result can be obtained by specifying a different data source. Instead of using the Shippers table, we could define the following SQL Query:

SELECT Shippers.ShipperID, Shippers.CompanyName, Shippers.Phone, COUNT(OrderID) AS Orders
FROM Shippers
INNER JOIN Orders
ON ShipVia = ShipperID
GROUP BY Shippers.ShipperID, Shippers.CompanyName, Shippers.Phone

While we could still keep the same destination (ShippersOrders table in the tempdb database), we would alter the transformation and use a single Copy Column between data source and destination. The resulting Transform Data Task should perform more efficiently. Keep in mind that lookup queries should be used only when necessary (e.g. when some of the data resides in a non-relational database, stored procedure must be used, or joins generate exceptionally large amount of data).

In our next article, we will discuss the remaining configuration options of Transform Data Task and will look into its more advanced option that allows multi-phase data processing.

» See All Articles by Columnist Marcin Policht