using lookups

Question

using lookups

Blair Dee

SSCommitted

Points: 1648
More actions
August 8, 2005 at 9:28 am

#168260

i am in the process of creating a DTS package which takes data from a fixed width text file and puts it into a SQL server table.
i have the basics of it done but one thing i cant figure out is how to use a lookup table. In the text file there is a field called attdr which contains a numeric code. but in the destination table i would like to insert the doctor name from the doctors lookup table.
I know that in the properties of the Transform Data Task, there is a lookups tab. and i created the lookup and named it ATTDRLookup. but when specifying all the transformations in the Transformations tab. i cant figure out how to access that lookup.
can someone please help?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Blair Dee SSCommitted Points: 1648 More actions · Answer 1

ok i have looked around for info on DTS lookups and i dont completely understand them yet but i have read that they can slow things down a bit and that for simple lookups its best to use TSQL statments.

so my DTS package consists of the Text File, the Destination table and a Transform Data Task connecting them. so what do i need to add in order to perform this lookup? an dexecute SQL task? where do i put it? what is the correct sql code for this?

any guidance woudl be greatly appreciated.

Blair Dee SSCommitted Points: 1648 More actions · Answer 2

ok i kind of figured out the lookups. in the lookups tab i set my query to:

SELECT DR_NAME

FROM Email_PhysicianMaster

WHERE (DR_NO = ?)

and then i went and created a new transformation of type ActiveX Script and set the source and destination fields. then i modified the activeX script to be the following:

'**********************************************************************

' Visual Basic Transformation Script

'************************************************************************

' Copy each source column to the destination column

Function Main()

DTSDestination("ATNDR_NAME") = DTSLookups("ATTDRLookup").Execute(DTSSource("Col016"))

Main = DTSTransformStat_OK

End Function

the problem is that when i add this lookup - processign 90,000 records takes about 5 minutes instead of 10 seconds. thats a really significatn slowdown that i cant afford to have.

so is there another way to do this? can someone PLEASE help me out?

Nhamblyn Grasshopper Points: 18 More actions · Answer 3

Hi Blair,

Not sure of the full problem you are trying to overcome, however it does sound a little more efficient toactually load the text file into a temp table then populate the main table with a join. Should only really use the lookups when the efficient use of a join is not possible. If you were having to actually transform a large number of columns through the reference to alternate small reference tables then lookups come into there own.

cheers

Nigel

Blair Dee SSCommitted Points: 1648 More actions · Answer 4

thank you for the reply! so let me see if i have got it right -

you are saying that i should have a transform data task to get the data from the text file to a temp table, and then a an execute SQL task from the temp table to the destination table?

if that is correct then i will give it a go.

thanks again for your reply.

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 5

Blair,

Nigel's solution is probably the preferred method. I have found that using Lookups tends to be slow at best!

Good luck,

Darrell

mike gercevich SSCrazy Points: 2961 More actions · Answer 6

One thing to keep in mind while using a Lookup, you can cache your lookups... now, of course this is only going to offer performance gains if you are actually looking up the same data multiple times.

I have found that if you use the locking hint WITH (NOLOCK), your performance will greatly improve.

With a well designed table including nice and tidy indexes, performance hurdles can usually be overcome. It all depends on what you are attempting to do in order to derive the best solution.

-Mike Gercevich