How can i perform a two dimensional lookup through DTS. Therefore I
want to pick a value from a table by providing primary key and column
name. My table is having 50 columns. I can implement the functionality
using 50 lookups but that seems like a bad idea.

Thanks,
PM

Do you mean

SELECT col1 FROM Table WHERE KeyCol = ? and Col2 = ?

How to Use Lookups in DTS
(http://www.sqldts.com/default.aspx?277)



"pm" <mittal.pradeep (AT) gmail (DOT) com> wrote

Thanks for the reply. I am looking for this kind of query.

select ? from table where keycol = ?

The two question marks are my x and y coordinates to get the value. I
referred this as kind of two dimensional lookup.

Pls let me know if you have any ideas.

PM

Allan Mitchell wrote:

I want to add one more thing. I know how to dynamically set the query
for lookup in the work flow activex of data pump task. But my
requirement is to dynamically set the query in the transformation
active x.
To be more specific, I have table like this.

pk col1 col2 col3 col4 col5 col6 col7 col8 col9 col10
1 yen usd krw usd yen 100 34 56 78 78
2 usd usd yen krw sgd 56 89 56 12 56
3 usd idr usd sgd yen 78 67 66 80 45
4 yen yen usd sgd sgd 67 78 99 90 67


where pk is the primary key
col1-col5 is currency for corresponding amounts
in col6 -col10

Aim is to convert all data in col6-col10 in the same currency which is
usd. For doing this while executing the data pump task, for every row
for every col from col1-col5, I need to get FX rate in the fx rates
table.

So i need to have dynamic query defined for lookup while doing the
transformation itself.

PM

What you are asking here is for a field (undefined as yet) to be
retrieved based on another as yet undefined value.

What do you intend to pass in to the Lookup function to tell the lookup
what to retrieve.

This is AFAIK not possible.




"pm" <mittal.pradeep (AT) gmail (DOT) com> wrote

Surely your FX rate lookup would accept a "From" and "To" currency and
return you a rate?


SELECT rate FROM dbo.FXTable WHERE FromCcy = ? AND ToCcy = ?




"pm" <mittal.pradeep (AT) gmail (DOT) com> wrote