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