Using Data Transformation Services: Using DTS Lookups, Introduction - DTS Lookup returning multiple values

(Page 4 of 4 )

In this next example, you are given the last name of the author. You are now required to find the author's full name (a combination of first name and last name), and the his state of residence. You may also notice that there is only one placeholder ? mark.

SELECT     state, au_fname + au_lname AS ' fullname'
FROM         authors
WHERE     (au_lname = ?)

Multiple values example

A similar sequence of steps is used in this example as well. Only some critical steps are shown here. In this case according to the above sql script, we use one source parameter, au_lname and generate the state and fullname from the query which, in its finished form, would look like this.

The query needed to obtain the input/output of the above is provided by creating the lookup query as shown here.

Now, the ActiveX script that makes the transformation is shown here. The left hand pane shows the package details, where the source, the destination, and the lookups are all shown. The script is also shown below the picture.

'**********************************************************************
'  Visual Basic Transformation Script
'************************************************************************
'  Copy each source column to the destination column
Function Main()
IFname=DTSLookups("Multi").Execute(DTSSource("au_lname"))
DTSDestination("fullname")= IFname(1)
DTSDestination("state")= IFname(0)
Main = DTSTransformStat_OK
End Function
'***********************************************************************

In the above script, the variable IFname is an array, holding the values the query returns in the array, accessed by the index. For example, IFname(0) holds the "state", IFname(1) holds the "fullname", and so on. This completes the package design and it can be executed. The result of this execution is shown in the next picture.

Summary

In this tutorial, two examples of using DTS lookups were described, one of them in detail. While the examples use one of the existing connections, they could have used a new connection. Although an example of a select query was shown, they could also be used in queries that modify data. The steps are quite detailed, and it is relatively easy to implement in the designer, once the syntax is correctly understood. However, the underlying connection should support the parametric representation. For example, this would not work with text files.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

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!