I’ve always been in favor of an orthodox strategy when it comes to applying SQL Server updates which often goes like:
- Instead of installing SQL Server Cumulative Updates, wait for release of service packs
- When a service pack is released, install it in phases starting from the non-production environment (i.e. DEV, UAT) to eventually roll it out on production
However, I was recently involved in a project whereby I needed to upgrade a client’s SQL Server 2008 based MIS environment to SQL Server 2016. The MIS environment consisted of your typical Microsoft BI stack services. This was going to be a side-by-side upgrade which meant we were to set up the SQL Server 2016 MIS version on a new box without interfering with the then SQL Server 2008-based MIS production environment. It just so happens that days prior to the start of our project, SQL Server 2016 Service Pack 2 (SP2) was released and in the interest of time and the client trying to avoid having to bring in consultants again at a later time for applying SP2, it was decided that we should roll out SP2 as part of our project delivery. As you might have guessed it and as self-fulfilling prophecies go, the installation of SQL Server 2016 Service Pack 2 did not yield positive results. This is because soon after the installation, our Integration Services Catalog packages that executed Analysis Services Processing Tasks started returning the following error message:
In this article, I will demonstrate the effects of SQL Server 2016 Service Pack 2 on an environment that relies on SSIS to process SSAS cubes. I also present you with different options that you could utilize to resolve some of the issues that come about as a result of installing SP2.
SSAS and SSIS solutions
Figure 1 shows my sample Demo cube.
The cube solution shown is processed using a SSIS package shown in Figure 2.
This package is then deployed as a project into the Integration Services Catalog as shown in Figure 3.
In addition to the SSAS and SSIS solutions, I have also created several snapshots of the VirtualBox virtual machine that I will be using as part of the demo. As shown in Figure 4, I have broken my snapshots according to type of updates that I have applied to my SQL Server 2016 instance.
Snapshot 1-2: SQL Server 2016 RTM & SP1
In the SQL Server 2016 snapshots running both RTM and SP1, I’m able to successfully execute my SSIS package directly from the Integration Services Catalog without any failures as shown in Figures 6.
Snapshot 3 – SQL Server 2016 with Service Pack 2
Unfortunately, once we upgrade our SQL Server 2016 instance from Service Pack 1 to Service Pack 2, the execution of my sample SSIS package from the Integration Services Catalog starts running into errors.
The details of the errors are outlined in Figure 8.
Options for resolving missing Microsoft.AnalysisServices.AdomdClientUI.dll assembly error
The options for resolving the missing Microsoft.AnalysisServices.AdomdClientUI.dll assembly error ranges from simple to complex.
-
Option 1: Replace SSIS package cube processing steps with XMLA
If the package that throws an error is as simple as my demo package in that it performs only one task – processing of analysis services cube using Analysis Services Processing task – you could replace that SSIS package step with XMLA script. The XMLA script could be called as part of SQL Server Analysis Services Command within a SQL Server Agent job step, as indicated in Figure 9.
Figure 9 The benefit of this approach is that your SQL Server 2016 instance retains its latest service pack update and the downside being that you may require some additional time for converting Analysis Services Processing tasks to XMLA scripts.
-
Option 2: Rollback to SQL Server 2016 Service Pack 1
Obviously, you could have complex SSIS packages with several Analysis Services Processing Task steps that attempting to convert them to XMLA scripts could take days and further delay your project go-live. Thus, in such a scenario you are better off simply rolling back to Service Pack 1. If your SQL Server 2016 instance is hosted on a VM like in my demo instance, then you could just rollback your virtual machine to a previous snapshot containing Service Pack 1. However, if your instance is not sitting on a virtual machine or you don’t have snapshots, you could implement a rollback by uninstalling Service Pack 2 as shown in Figure 10.
Figure 10 -
Option 3: Assembly binding redirect
The final option involves manually changing the machine configuration file – although this option works, it is my least favorite because it can easily lead to mistakes that can affect the entire server. Nevertheless, the machine configuration file is likely to be familiar to web developers than BI developers but it essentially contains server-wide configurations that often overrides configuration of web.config file. Thus, one way of dealing with the missing Microsoft.AnalysisServices.AdomdClientUI.dll assembly error would be to install either version 12 or 14 of Microsoft.AnalysisServices.AdomdClientUI.dll and then adjust the machine configuration file to look at version 12 or 14 whenever Integration Services Catalog is looking for version 13 of Microsoft.AnalysisServices.AdomdClientUI.dll assembly file.
You can easily find version 12 and 14 of Microsoft.AnalysisServices.AdomdClientUI.dll by simply doing a file search on your root drive as shown in Figure 11.
Figure 11 Once you have located the version of Microsoft.AnalysisServices.AdomdClientUI.dll assembly file you want to install, you can then add it into the global assembly cache using the GACUTIL.exe. In my case, I have decided to install version 12 of the Microsoft.AnalysisServices.AdomdClientUI.dll assembly file using the command:
C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools>gacutil /i “C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies\Microsoft.AnalysisServices.AdomdClientUI.dll”
As it can be seen in Figure 12, the assembly was successfully loaded.
Figure 12 Thereafter, I get the metadata of the assembly I’ve just installed using the lr option of GACUTIL. Take special note of the publicKeyToken value as we will need it when editing the machine configuration file.
Figure 13 Finally, I switch over to the machine configuration (machine.config) file which in my VM is located under C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config. I then make a copy of the machine.config file and edit the original configuration file by replacing <runtime/> element with the following:
123456789<runtime><assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"><dependentAssembly><assemblyIdentity name="Microsoft.AnalysisServices.AdomdClientUI" publicKeyToken="89845dcd8080cc91"culture="neutral"/><bindingRedirect oldVersion="13.0.0.0" newVersion="12.0.0.0" /></dependentAssembly></assemblyBinding></runtime>Following the restarting of all my SQL Server services, I received a successful message when I next ran my SSIS package as shown in Figure 14.
Figure 14 -
Option 4: Apply cumulative update for Service Pack 2
At the time of writing this article, a cumulative update (CU) was released that fixes, amongst other things, the missing Microsoft.AnalysisServices.AdomdClientUI.dll assembly file error. The CU can be downloaded from this link.
References
- Schedule SSAS Administrative Tasks with SQL Server Agent
- Rollback procedure for SQL Server updates
- Create an Analysis Services Job Step
Sifiso's LinkedIn profile
View all posts by Sifiso W. Ndlovu