INF: How to Return Multiple Columns in a DTS Lookup Query

• Microsoft SQL Server version 7.0

SUMMARY

The Data Transformation Services (DTS) Lookup object provides the ability to 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.

MORE INFORMATION

The following sample code demonstrates how to execute a multiple column lookup, retrieve the values for each column returned into variables and then use those variables within the transformation. The sample is based on the authors table in the pubs database and uses the au_id column to lookup and return the au_lname and au_fname columns.

The Lookup query is named "Two Column Lookup" and is defined as:

SELECT au_lname, au_fname
FROM authors
WHERE au_id = ? 

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 

Additional query words:

Keywords : SSrvProg kbSQLServ700
Version : winnt:7.0
Platform : winnt
Issue type : kbinfo