Analysis Services Processing SSIS Task in SSDT 2015/2017 fails when TargetServerVersion is set to SQL Server 2016

I ran into this problem a while ago at a client. They upgraded from Visual Studio 2013 to 2015 and the SSAS processing tasks started to error out immediately. The solution turned out to be setting the TargetSeverVersion to anything but SQL Server 2016. In this case, it was set to 2014 and that fixed the error.

Recently I ran into this post https://twitter.com/SQLKohai/status/994335086425399297 by Matt Cushing (@SQLKohai) and decided to dig in more.  Initially when I tested it, all was working fine. After I installed SSDT 2015 to test, I started getting the same error in SSDT 2017.  I played around with a DLL and got SSDT 2017 to work with all TargetVersionServers again. At the end I managed to break it again after I went through an uninstall and reinstall of all versions of SSDT. The reason I did the reinstall of SSDT was that I thought I might have had a broken registry entry that I was hoping the installation would fix. This did not work!

Before I get started I would like to remind you that:

As far as SSAS is concerned, the XMLA generated by the processing task works against SSAS 2012 to 2017. Unless if you are worried about other tasks in your SSIS project, setting the TargetServerVersion property of the SSIS project to SQL Server 2017 or SQL Server 2014 solves the problem and does not make any difference for SSAS.

If you are interested to read more details about different things I tried, read on:

When I tried SSDT for Visual Studio 2017 (version 15.6.7), the problem seemed to have been fixed. The SSAS processing task worked with all TargetServerVersions.

Then I installed SSDT for Visual Studio 2015 to test the problem. The default setting for the SeverTargetVersion for the latest version of SSDT for VS 2015 (14.0.23107.0) is SQL Server 2017. Once I changed it to SQL Server 2016, I ran into the same error others had reported. I get an error message right away if I click on an existing SSAS processing task and I am not able to set up a new SSAS processing task.

Here is the error after double clicking on an existing task:

Method not found: ‘Microsoft.AnalysisServices.ErrorConfiguration Microsoft.AnalysisServices.Commands.BatchCommand.get_ErrorConfiguration()

If I run the package, it fails with the following error:

[Analysis Services Processing Task] Error: Could not load file or assembly ‘Microsoft.AnalysisServices.AdomdClientUI, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.

Doing some research, it seems like others had run into this problem as well. See below: https://www.sqlservercentral.com/Forums/PrintTopic1931084.aspx

The problem seems to be happening because AdomdCientUI.dll had somehow become unregistered. Some were able to solve the problem by copying it into another folder where it was expected to be.

Then I searched for dll and I found copies here:

C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\PrivateAssemblies\Business Intelligence Semantic Model

C:\Program Files (x86)\Microsoft Visual Studio\2017\SQL\Common7\IDE\CommonExtensions\Microsoft\BIShared

I decided to try to register the DLL before I try the copy solutions. I copied the dll to C:\Windows\System32 directory and tried to register it:

C:\WINDOWS\system32>regsvr32 Microsoft.AnalysisServices.AdomdClientUI.dll

I got this error:

Here is the version information from the DLL:

Then I decided to use ProcessMonitor to see if can find out where VS is looking for the file as someone had suggested in the previous link.

For this part, I used SSDT 2017.

It is interesting to see that when I switch the TargetServerVersion, the dll is accessed differently:

First starting with TargetServerVersion = SQL Serve 2017

1. When I double click on the package, there are no entries in ProcessMonitor for the dll.
2. When I click on the task, there are no entries for the dll.
3. When I run the task, a lot of entries are generated. Some show a Result of PATH NOT FOUND. Interestingly the processing task runs successfully.

Now switching the ServerTargetVersion = SQL Server 2016:

Right after the switch: Notice the process name is DtsDebugHost.

After double clicking on the package: Note that the process name is deven.exe.

After double clicking on the package, I get the error message as before

but no entries show up in Process Monitor.

If I run the package it runs successfully!

While I cannot actually edit the existing processing task, I can add a new one:

Note the warning at the bottom. At this point, it won’t let me save it.

Copying the DLL over:

Source:

C:\Program Files (x86)\Microsoft Visual Studio\2017\SQL\Common7\IDE\CommonExtensions\Microsoft\BIShared

Copied To:

C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Tasks

I still got the same error message. This time the ProcessMonitor results were a little bit different:

Same results from SSDT 2015:

The path that DtsDebugHost is looking for does not exist. I decided to crate the gnarly looking folders and see what happens:

I chose the first entry in ProcessMonitor. If you right click on a row, you can get the properties and copy the path so that you can create the exact same folder. I copied the DLL from

C:\Program Files (x86)\Microsoft Visual Studio\2017\SQL\Common7\IDE\CommonExtensions\Microsoft\BIShared

to

1. C:\WINDOWS\Microsoft.Net\assembly\GAC_32\Microsoft.AnalysisServices.AdomdClientUI\v4.0_13.0.0.0__89845dcd8080cc91
2. C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.AnalysisServices.AdomdClientUI\v4.0_13.0.0.0__89845dcd8080cc91
3. C:\WINDOWS\Microsoft.Net\assembly\GAC_32\Microsoft.AnalysisServices.AdomdClientUI\v4.0_14.0.0.0__89845dcd8080cc91
4. C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.AnalysisServices.AdomdClientUI\v4.0_14.0.0.0__89845dcd8080cc91

and everything started to work fine for SSDT 2017! The processing task ran successfully!

Unfortunately I still got the same error from SSDT 2015.

Note: The folders may be different on your machine. Use Process Monitor to find out where DTsDebugHost is looking for the DLL.

Uninstalling SSDT 2017 and others…

It looks to me that there is still some kind of registry problem. Remember that SSDT 2017 was working fine until I installed SSDT2015. I am going to uninstall SSDT 2017 and 2015 and any other remnants and start over!

This is what I had in Control Panel before I started uninstalling. I have four version/flavors of SQL Server Data Tools! Time to clean up!

First uninstalled Microsoft SQL Server Data Tools for Visual Studio 2017 (SSDT).

Then I uninstalled Microsoft SQL Server Data Tools – Visual Studio 2017.

---

Interestingly, during this install there is a mention of SQL Server 2016 AMO.

Then I uninstalled Microsoft SQL Server Data Tools – enu (14.0.61712.050), SQL Server Data Tools – Visual Studio 2015, and Prerequisites for SSDT.

Finally I uninstalled all versions of Visual Studio Tools for Applications. I think at some point I had installed this for a test.

 

Reinstalling

You can get SSDT for VS 2015 and VS2017 from here.

https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017

I started with SSDT for VS 2015. Unfortunately, the error is back! I am at a loss with this one!

I installed SSDT for Visual Studio 2017. Same error is back for SSDT 2017. I can still run the package successfully though.

Conclusion:

I believe this is some kind of bug in SSDT for Visual Studio 2015 but I don’t know how to fix it.

Some people (including myself for the first round) were able to get around this problem by copying the ADOMDClientUI.dll to a couple of folders.

Ultimately if you need the TargetServerVersion to be set to SQL Server 2016 for other parts of your SSIS project, you can get around this problem by setting it to SQL Server 2017 or 2014. All these setting generate the same XMLA processing command for SSAS.