DTS Lookups - Type Mismatch error

Question

DTS Lookups - Type Mismatch error

StefanJ

SSCarpal Tunnel

Points: 4713
More actions
November 21, 2003 at 5:28 am

#107383
I am exporting to Excel based on query against an SQL Server table and a lookup against an Access table.
The lookup works fine if I have only one field but as soon as try to use an array I get an error against the line where I assign the array:
Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Error Description:Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Type mismatch: 'arrAddresses'
If I set the max error count to 9999 then the data loads correctly.
Could this have something to do with not having a matching row in the Access table.
My code as follows:
```
option explicit



Function Main()



dim arrAddresses



DTSDestination("ChildName")=DTSSource("ChildName")

DTSDestination("ChildNumber")=DTSSource("ChildNumber")



arrAddresses = DTSLookups("lkp_CustomerDetails").Execute(DTSSource("ChildNumber"))

DTSDestination("Address2")=arrAddresses(0)

DTSDestination("Address3")=arrAddresses(1)



Main = DTSTransformStat_OK

End Function
```

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65144 More actions · Answer 1

Could be. DTSLookups will return empty variant if no data. To protect against error use

If IsEmpty(arrAddresses) Then

...

else

..

End If

Far away is close at hand in the images of elsewhere.
Anon.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

quote:
Could be. DTSLookups will return empty variant if no data. To protect against error use
If IsEmpty(arrAddresses) Then
...
else
..
End If

now would you please explain me this 'mickel' - 'muckel' thing, David ...or I'll post something in German.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David Burrows SSC Guru Points: 65144 More actions · Answer 3

Auch Mench, Frank.

Are you referring to the quote or the dialogue in the other thread?

Far away is close at hand in the images of elsewhere.
Anon.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

quote:
Auch Mench, Frank.
Are you referring to the quote or the dialogue in the other thread?

The other thread.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David Burrows SSC Guru Points: 65144 More actions · Answer 5

quote:
many a mickel mucks a muckel sic!

It's one of those obscure scottish (and I'm not scottish) phrases about money. As far as I know loosely translated it means "many pennies make pounds" or "look after the pennies and the pounds will look after themselves.

I must be getting to know you as I can't resist jokey posts with you, must be the child in me

Far away is close at hand in the images of elsewhere.
Anon.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

quote:
I must be getting to know you as I can't resist jokey posts with you, must be the child in me

Thanks for explanation!

Keep that mentality, it's unpayable.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

StefanJ SSCarpal Tunnel Points: 4713 More actions · Answer 7

quote:
If IsEmpty(arrAddresses) Then

Many thanks, that did the trick

Stefan