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.

Why do you say you can't use Max() function in SSIS against a non-numeric column? What is the error or result?

Did you look at the sort trasformation with the 'Remove rows with duplicate sortvalues' option marked?

|||

I have tried to use Max() function to aggregate my data but I can't do it on a string column. I only have choice between "group by", "count" or "count distinct" aggregation functions.

And I've found this information in msdn : http://msdn2.microsoft.com/en-US/library/ms138031.aspx : "... In contrast to the Transact-SQL MAX function, this operation can be used only with numeric, date, and time data types."

I've tried your solution and it works very fine, thank you very much, you solved my problem!

Arnaud.