Installation failure: “Could not load file or assembly ‘Microsoft.AnalysisServices.SharePoint.Integration.dll’ . . .

We’ve been hearing reports of this failure for several weeks, but we’ve not been able to get a good repro until a week ago. And it was the most “most reported bug” at TechEd — if there is such a category — but it looks like we have a solution after many hours of hard work.

(With my thanks to Fernando Delgado, our excellent setup dev for working up this solution)

(RTM build 10.5.1600.1) As a picture is worth a thousand words, here is the error:

This failure occurs when installing PowerPivot (either “existing farm” or “new server”) after a previous SSAS installation has been done on the machine.

Cause:

In SQL Server 2008 R2, the msmdsrv.exe process (which is the SSAS engine) includes a reference to the PowerPivot SharePoint integration dll (as do other components). Since the normal SSAS installation does not include the SharePoint integration dll, the registration marks the .NET assembly cache as failed. Later on, when you install PowerPivot, we copy the SharePoint integration dll to the right place, but the .NET assembly cache has already been marked failed from the previous SSAS installation and .NET does not attempt to load it (and you get the error message above).

Solution:

A. Best solution: If using “New Server” PowerPivot installation option then delay installing any instances besides SQL Server RDMBS until after you have PowerPivot installed and configured. That way you never hit this error condition. Doing a SQL Server RDBMS installation only for “existing farm” is OK – it is the installation of other non-RDBMS components that is the root cause of the issue.

B. If you have an RDBMS only instance(s) installed and are getting ready for a PowerPivot installation (“New Server” or “Existing Farm”): Save the following text to a file named setup100.exe.config (no .txt extension):

1. Save the following text to a file named setup100.exe.config (no .txt extension):

   <?xml version="1.0" encoding="utf-8" ?>
   <configuration>
     <runtime>
       <disableCachingBindingFailures enabled="1" />
     </runtime>
   </configuration>

2. b. Copy the file to the folder: %ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\x64\ and install PowerPivot for SharePoint.

C. You have already installed PowerPivot and gotten the “Could not load file or assembly” error above, then:

1. Uninstall the PowerPivot instance of Analysis Services, this will also uninstall PowerPivot for SharePoint components.
2. Reboot the machine
3. Run scripts to re-enable PremiumSite feature. See http://powerpivotgeek.com/2010/06/14/uninstalling-powerpivot-does-what-arg/
4. Reboot the machine
5. Copy the setup100.exe.config file, as outlined in (B) above, to %ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\x64
6. Install PowerPivot for SharePoint.