CODEWITHSUNDEEP
sql-serversql-server-2008ssis
bitshift
bitshift

Reputation: 6842

BIDS 2008 - Lookup value in source against two columns in lookup table

Have a dataflow task where I need to lookup a value in a table that could exist in one of two columns. Im not sure how to configure the lookup to do this. Ive only used one value in the past.

If I was just using tsql, I would use a case statement and join on the lookup table, with the result being the matched value.

In other words, CASE WHEN source.column_a = lookup.column_a THEN lookup.column_a WHEN source.column_a = lookup.column_b THEN lookup.column_b END

How do i implement the above logic in the lookup transform?

Upvotes: 0

Views: 54

Answers (1)

Vinnie
Vinnie

Reputation: 3929

You can use a union as the source in the lookup transformation, then use a single transform:

SELECT id, column_a FROM dbo.Table
UNION SELECT id, column_b from dbo.Table

Conversely, you can use two lookups with the first looking at column_a and pass the no match output to a second lookup with column_b, then union the results.

Upvotes: 1

Related Questions