Using Data Transformation Services: Using DTS Lookups, Introduction - Example continued

(Page 3 of 4 )

On clicking the ellipsis button the following query designer pops up. Herein, the previously mentioned query is configured as shown. The details of how this is carried out have already been discussed in previous DTS tutorials. This is the same query we started with in the beginning.

The 'Source' and 'Destination' needed for this transformation are shown here.

 

 

Now in the above windows, accessing the General tab leads to the Activex scripting window shown here. The picture is not clear, but the package objects can be seen in the left pane.

 

The ActiveX script that makes this transformation is shown in the next paragraph. The variable iTitle is assigned the value returned by the lookup query, "GetTitle" by passing the two arguments au_lname and state. The syntax is: returnValue=DTSLookups().Execute(param1, param2). This is then passed to the DTS Destination.

'**********************************************************************
'  Visual Basic Transformation Script
'************************************************************************
'  Copy each source column to the destination column
Function Main()
iTitle=DTSLookups("GetTitle").Execute(DTSSource("au_lname") ,

DTSSource("state"))
DTSDestination("title")=iTitle
Main = DTSTransformStat_OK
End Function
'************************************************************************

As discussed in previous tutorials, it can be parsed and tested in the Activex designer interface. This completes the Transform Data task and results in the previous window shown earlier (the finished product!). When this package is run, the MS Access database table gets populated as shown in the next picture, which is the result required.

Next: DTS Lookup returning multiple values >>
 

More MS SQL Server Articles
More By Jayaram Krishnaswamy

Comments On: Using Data Transformation Services: Using DTS Lookups, Introduction

· Most of the articles on DTS written by me including the present one use the...    · Hi,I am pretty new to DTS but not to ETL. One of the major requirements I have...   >>> Post your comment now!