A duplicate attribute key has been found when processing SSAS Cube Dimension

I had a strange issue at a customer on a SSAS cube.
I was trying to migrate the cube from dev environment to test, the processing of the cube starts to fail with this error message:
Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘Client’, Column: ‘Name’, Value: ‘JàRnïpâ’.
The thing is that in dev with the same data it was succeeding!!!!!!!
The error was coming from the cube collation, it was setup to accent insensitive and the source database was in Accent sensitive.
Here is the desription of the bug:
To create the agrregation of the cube, SSAS send “Select distinct” queries to the database, as the source database is in accent sensitive, here is the result:
JàRnïpâ
JaRnïpâ
–> those 2 entries are different for “select distinct” result with the option accent sensitive enabled!!
But for the cube, as it is in incensitive, those records are considered as the same!!!!
This is why you got back duplicate attribute error :
Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘Client’, Column: ‘Name’, Value: ‘JàRnïpâ’.
Damned!

Using .xlsx sources with SSiS (64 bit) on a 64 bit system

The only way I’ve found to run an SSIS data flow with an .xlsx as source and without changing the option ’64 runtime’ to false is to use as source an “OLE DB source” component in the data flow with this query:

select * FROM OPENROWSET (‘Microsoft.ACE.OLEDB.12.0’

, ‘Excel 12.0;Database=C:\Hello.xlsx’

, [Sheet1$]

)

By the way, to make it works, you have to install the ACE drivers(for 64 bit) from microsoft website on the machine where you execute your package. 🙂