COM error: mscorlib, An item with the same key has already been added

[JeanMartinL]

Hello,

We've patched our Report Server October 2020 with the November 18th patch and this fixed almost all our refresh issues.

However I still have one report which doesn't refresh correctly with the following error:

2020-11-30 14:36:40.5728|ERROR|32|Error Processing Data Model Refresh: SessionId: 077e51fd-6aab-4993-9dc2-29dae10409fa, Status: Error Failed to refresh the model, Exception Microsoft.AnalysisServices.OperationException: Failed to save modifications to the server. Error returned: 'COM error: mscorlib, An item with the same key has already been added..
COM error: mscorlib, An item with the same key has already been added..
The database operation was cancelled because of an earlier failure.
The command has been canceled.. The exception was raised by the IDbCommand interface.
The command has been canceled.. The exception was raised by the IDbCommand interface.
The command has been canceled.. The exception was raised by the IDbCommand interface.
The command has been canceled.. The exception was raised by the IDbCommand interface.
'.
   at Microsoft.AnalysisServices.Tabular.Model.SaveChangesImpl(SaveOptions options)
   at Microsoft.PowerBI.ReportServer.AsServer.TOMWrapper.RefreshModel(Database database)
   at Microsoft.PowerBI.ReportServer.AsServer.AnalysisServicesServer.RefreshDatabase(String databaseName, IEnumerable`1 dataSources, IEnumerable`1 parameters, String clientSessionId)
   at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.AnalysisServicesDataRefresh.RefreshDatabase(AsDatabaseInfo asDatabaseInfo)
   at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.<>c__DisplayClass34_0.<ExecuteActionWithLogging>b__0()
   at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.<ExecuteFuncWithLogging>d__33`1.MoveNext()

The report refreshes correctly in Desktop, it's only once deployed on Report Server that it fails.

Its sources are 2 SQL Server database (on the same server) and 6 Excel files (in the same folders). I remember having a similar issue in the past when having the same source set twice with difference cases but it doesn't seem to be the case here.

I've seen some threads (some quite old) saying that it can happen when merging sources with the same column names. At first glance it doesn't seem to be the case but it's a large report so it's not impossible. Knowning what is the duplicated key would certain help pin-point the issue but I'm not sure if that's possible.

Thanks!

[Denis_Zinukov]

Hello,

I had the same issue, to solve I renamed columns that use as keys in join operation. How I have new error

So I can't say that it can help. But, perhaps, it a good idea to try.

In my case joined quereis came from Query to folder with Excel files and query to Excel file.

Report Server version

[declanR]

Thanks, Denis. The first thing i did to fix the problem. Not working

[JeanMartinL · Accepted solution]

Thank you for the reply Denis. Unfortunately it didn't work for me but it might help someone else.

However I did find a solution to my problem.

Our report created its SQL data source in 2 distinct ways for some reason, using 1 or 2 lines:

Source = Sql.Database("HOSTNAME", "DATABASENAME"),
or
Source = Sql.Databases("HOSTNAME"),
DATABASENAME = Source{[Name="DATABASENAME"]}[Data],

Apparently when we merged 2 tables using different source creation methods it would crash in Report Server. I changed all our data source to use the single line method and now everything works.

That definitely sounds like a bug, I will create an issue report.

[declanR]

Thank you for the reply JeanMartinL. Unfortunately it didn't work for me but it might help someone else.