Microsoft KB Archive/249236: Difference between revisions
(importing KB archive) |
m (Text replacement - """ to """) |
||
| Line 60: | Line 60: | ||
The following sample code demonstrates how to execute a multiple-column lookup, to retrieve the values for each column that is returned into variables, and then to use those variables in the transformation. The sample is based on the authors table in the '''pubs''' database and it uses the au_id column to look up and to return the au_lname and au_fname columns.<br /> | The following sample code demonstrates how to execute a multiple-column lookup, to retrieve the values for each column that is returned into variables, and then to use those variables in the transformation. The sample is based on the authors table in the '''pubs''' database and it uses the au_id column to look up and to return the au_lname and au_fname columns.<br /> | ||
<br /> | <br /> | ||
The Lookup query is named | The Lookup query is named "Two Column Lookup" and it is defined as: | ||
<pre class="codesample">SELECT au_lname, au_fname | <pre class="codesample">SELECT au_lname, au_fname | ||
FROM authors | FROM authors | ||
| Line 67: | Line 67: | ||
The transformation is defined as: | The transformation is defined as: | ||
<pre class="codesample">Function Main() | <pre class="codesample">Function Main() | ||
DTSDestination( | DTSDestination("au_id") = DTSSource("au_id") | ||
astrName = DTSLookups( | astrName = DTSLookups("Two Column Lookup").Execute(DTSSource("au_id")) | ||
strLastName = astrName(0) | strLastName = astrName(0) | ||
strFirstname = astrName(1) | strFirstname = astrName(1) | ||
DTSDestination( | DTSDestination("au_lname") = strLastName | ||
DTSDestination( | DTSDestination("au_fname") = strFirstname | ||
DTSDestination( | DTSDestination("phone") = DTSSource("phone") | ||
DTSDestination( | DTSDestination("address") = DTSSource("address") | ||
DTSDestination( | DTSDestination("city") = DTSSource("city") | ||
DTSDestination( | DTSDestination("state") = DTSSource("state") | ||
DTSDestination( | DTSDestination("zip") = DTSSource("zip") | ||
DTSDestination( | DTSDestination("contract") = DTSSource("contract") | ||
Main = DTSTransformStat_OK | Main = DTSTransformStat_OK | ||
End Function | End Function | ||
Latest revision as of 13:51, 21 July 2020
Article ID: 249236
Article Last Modified on 1/14/2004
APPLIES TO
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2000 64-bit Edition
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q249236
SUMMARY
With the Data Transformation Services (DTS) Lookup object, you can retrieve data from locations other than the immediate source. A query and a connection is associated with the lookup. If the query returns more than a single column, the Lookup object returns the results in a zero-based array.
The following sample code demonstrates how to execute a multiple-column lookup, to retrieve the values for each column that is returned into variables, and then to use those variables in the transformation. The sample is based on the authors table in the pubs database and it uses the au_id column to look up and to return the au_lname and au_fname columns.
The Lookup query is named "Two Column Lookup" and it is defined as:
SELECT au_lname, au_fname
FROM authors
WHERE au_id = ?
The transformation is defined as:
Function Main()
DTSDestination("au_id") = DTSSource("au_id")
astrName = DTSLookups("Two Column Lookup").Execute(DTSSource("au_id"))
strLastName = astrName(0)
strFirstname = astrName(1)
DTSDestination("au_lname") = strLastName
DTSDestination("au_fname") = strFirstname
DTSDestination("phone") = DTSSource("phone")
DTSDestination("address") = DTSSource("address")
DTSDestination("city") = DTSSource("city")
DTSDestination("state") = DTSSource("state")
DTSDestination("zip") = DTSSource("zip")
DTSDestination("contract") = DTSSource("contract")
Main = DTSTransformStat_OK
End Function
Keywords: kbhowtomaster KB249236
