ssis lookup with derived columns?

Question

I just want to make sure i am doing this correctly.

Derived columns:

car
truck


Lookup (after derived column:)

1.Query: select * from dbo.store where A = ? and B = ?.  

2.In column mapping/or advanced parameters I map car to A and truck to B.

Correct? I ask this because I keep getting an OLE error.

no one knows how to use a lookup?? – tdjfdjdj Oct 14 '11 at 14:46 — tdjfdjdj, Oct 14 '11 at 14:46
What is the error you are receiving? – billinkc Oct 14 '11 at 18:17 — billinkc, Oct 14 '11 at 18:17

score 4 · Accepted Answer · answered Oct 14 '11 at 23:28

While I'm waiting to hear back on the error message, my assumption at this point is that you are using the lookup component incorrectly. Your query syntax presumes there will be a query executed for every row flowing through the component.

The way the lookup component works with default configuration is that during the pre-execute event, the query is run against the target database and all the results are pulled back. Which is why as a general best practice, you should only pull back the columns you need. The query results are then cached locally and used in the lookup task as a dictionary-style lookup.

These screens change between 2005 and 2008 but you can figure it out.

Select cache mode enter image description here

Define connection and your source query. enter image description here

Wire up columns. This is how it will find matches instead of the parameterization method you were attempting. In this example, I am connecting truck and car to A and B and retrieving the foo column enter image description here

ssis lookup with derived columns?

1 Answers1

Linked