Hi Guys,

I am sure you can help me. I am new to the DTS and working on multiple column lookup under ActiveX script. I am using SQL Server 2000 with no sevice pack.

Lookup Query:
SELECT STATUS_DATE,STATUS_CODE,DELIVERY_DATE
FROM PRODUCT_ORDER
WHERE (PRODUCT_ORDER_ID = ?)

The Transformation is
Function Main()
aProduct = DTSLookups("ProductPO").Execute(DTSSource("ORDER_N UMBER"))
POStatusDate = aProduct(0)
POStatusCode = aProduct(1)
PODeliveryDate = aProduct(2)

DTSDestination("PO_NUMBER") = DTSSource("ORDER_NUMBER")
DTSDestination("PO_DATE") = aProduct(0)
DTSDestination("PO_STATUS") = aProduct(1)
DTSDestination("PO_DELIVERY_DATE") = aProduct(2)
Main = DTSTransformStat_OK
End Function

When i run this transformation I am getting an error that seems to be with multiple column lookup.

I would appreciate any help.
Thanks.

Can someone help me please!

If possible I would try and rewrite this query to do a join rather than a lookup. If you need pump over the data into a staging table and then do the join. Unless you have to pump over large volumes of data this is usually faster than do a lookup

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com