DTS Lookup not working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS Lookup not working

Author

Topic

talleman
Starting Member

3 Posts

Posted - 2004-01-22 : 14:44:29

I'm doing my first LookUp in a simple DTS transformation.

Here's my lookup:
SELECT MetroIndex
FROM Metro
WHERE (MetroCode = '?')

Here's my Transformation:
Function Main()
DTSDestination("OrgID") = DTSSource("OrgID")
DTSDestination("MetroIndex") =
Cint(DTSLookups("lkp_MetroIndex").Execute(DTSSource("MetroCode")))

Main = DTSTransformStat_OK
End Function

When I test the transformation, it runs without errors, but it gives me
results like this:
MetroIndex OrgID
0 abq100001
0 ...
0 ...

The lookup should be providing an integer other than 0 for the
MetroCodes I am feeding it. The first line has a MetroCode of 'abq',
and when I feed that into the LookUp instead of the question mark (?),
it returns MetroIndex 209. So why is my lookup returning 0 in my
transformation? Am I referring to it incorrectly? I tried changing the
line with the lookup to this:

Dim arrMetro
arrMetro =
DTSLookups("lkp_MetroIndex").Execute(DTSSource("MetroCode"))
DTSDestination("MetroIndex") = arrMetro(0)

...like the example on sqldts.com, but it gives me a RunTime error,
"Type mismatch: arrMetro" on the last line.

Anybody see what I'm doing wrong?

TIA,
Tab

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-23 : 16:42:15

Some things of this kind may not work with v7.0.