Lookup Transform

I want to do something relatively simple with SSIS but can't find an easy way to do this (isint it always the case with SSIS )

I have a column lets say called iorg_id, and I want to lookup the matching rows for this col in a table.

In this table iorg_id may have several potential matching rows. In this table there is another col called 'Amount'.

I want to retrieve for each iorg_id the matching iorg_id in the other table but only the row with the largest value in the 'Amount' col.

I couldn't find a way to do this all in the Lookup Transform.

I can match the iorg_ids and retrieve the Amount column, but can't find a way just to retrieve the matching row with the largest value in the Amount col. The only way I can think to do this is then run the output from the Transform through an Aggregate function and determine the Max (although haven't tested this yet).

Seems strange to me in that the SQL in the Advanced tab gives me something like:

select * from
(select * from [dbo].[Table1]) as refTable
where [refTable].[iorg_id] = ?

where I believe the first 'select *' is retrieving all the cols that are listed in the LookupColumns list in the Columns tab.

I thought I would be able to amend this to something like:

select max(amount) from
(select * from [dbo].[Table1]) as refTable
where [refTable].[iorg_id] = ?

but I get a metadata type error.

So, questions are:

Is it possible to do this all in the Lookup Transform are do I have to use the Aggregate function as I think ?

Why is it not possible to amend the sql in the Advanced tab to manipulate the returned data ?

If I understand the problem correctly, couldn't you simply use (in the Lookup) some SQL similar to: -

SELECT iorg_id
, MAX(amounT)
FROM <YourTable>
GROUP BY iorg_id

A match on iorg_id should then give you what you want

|||

Yes, Paul you're right. Thanks.

My colleague had actually pointed that out to me in the interim.

Sometimes it's difficult to see to the wood for the trees !