Problem Of Unicity Constraint In A Data Flow

Hi,

I'm trying to import data from an xml file into a table in my database and I have a problem of unicity constraint in my table.

Here is an example of the data flow in my file :

CITY LOCATION_ID CATALOG_SAISON

PARIS 12 S06

PARIS 12 S07

NEW YORK 15 S06

BERLIN 14 S08



The primary key in my destination table is the two first fields CITY/LOCATION_ID, so I would like to have only one CATALOG_SEASON per CITY/LOCATION_ID. That's why I want insert the following flow in my destination table:

CITY LOCATION_ID CATALOG_SAISON

PARIS 12 S06

NEW YORK 15 S06

BERLIN 14 S08



In Transact-SQL, I can do it easily with an agregation on CATALOG_SAISON field, using a max() function for example : SELECT CITY, LOCATION_ID, max(CATALOG_SAISON) FROM temp_table GROUP BY CITY, LOCATION_ID

In SSIS I would like to do the same with an agregation task but I can't use max() function with a string field. So I can't agregate this field and the only solution I have found now is to create a temporary table in my database to execute the sql request and retrieve data I need.

I'm sure there's a best solution in SSIS, could you help me?

Thanks

Regards

Arnaud Gervais.