Having problems with multiple columns in ActiveX script. I've tried
everything I've found on Microsoft website, numerous newsgroups and
websites. I'm just wanting to do a data transformation and I'm new to
DTS but this will save me hours and hours of work.

The lookup as follows:-

SELECT addr_detail.addr_line1,
addr_detail.addr_line2,
addr_detail.addr_line3,
addr_detail.addr_city
FROM addr_detail INNER JOIN
address_type_ref ON
addr_detail.addr_company_code = address_type_ref.atr_company_code AND
addr_detail.addr_customer_code =
address_type_ref.atr_customer_code AND
addr_detail.addr_number =
address_type_ref.atr_addr_number
WHERE (address_type_ref.atr_company_code = 1) AND
(address_type_ref.atr_addr_type = 'I') AND
(address_type_ref.atr_customer_code = ?)



The script in it's entirety is :-


'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()

arr = DTSLookups("Address_Lines").Execute(DTSSource("cus _code"))

DTSDestination("OKCUA1") = arr(0)
DTSDestination("OKCUA2") = arr(1)
DTSDestination("OKCUA3") = arr(2)
DTSDestination("OKCUA4") = arr(3)

Main = DTSTransformStat_OK
End Function


I always get the message Type mismatch 'arr' with an error code 0
pointing to the line where I set "OKCUA1".

I've tried putting in dim arr(4) but then it doesn't like the
DTSLookups line.

I've tried .Value when setting the Destination values and even CStr
for the elements.

Any help would be greatly appreciated.

Regards

Stuart Wallace

A type mismatch seems fairly straight forward as an explanation.

Using value and type casts should work, e.g.


DTSDestination("OKCUA1").Value = CStr(arr(0))

Obviously change CStr if OKCUA1is not really a string, and also make sure
the value in the array is convertable to a string. NULL for example is not.

If this is not working, then add some code to tell you what is being
returned. What is the value and type of the data in the array.

The VBScript VarType function may help you determine the type in the array,
although it shouldn't be too hard since it should match the lookup query of
course.



--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

"Stuart" <stuwallace (AT) btinternet (DOT) com> wrote