BizTalk Server 2020 BAM Configuration: Could not load file or assembly Microsoft.SqlServer.SQLTask

I recently published a blog post about BizTalk Server 2020 BAM Configuration: Microsoft SQL Server Integration Services (SSIS) is not installed on the local machine. This article is a direct continuation of that one, as the issues I encountered during the installation went far beyond that initial error.

Although SQL Server Integration Services and SQL Server Analysis Services were correctly installed on the SQL Server, and SQL Server Management Studio (SSMS) 18.4 was present on the BizTalk Server machine, additional problems continued to surface.

Nevertheless, while trying to configure BAM features, I was getting the following error message:

Could not load file or assembly ‘Microsoft.SqlServer.SQLTask, Version=13.0.0.0′,Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.

Once I solve that one, I endup getting another one:

Could not load file or assembly ‘Microsoft.SqlServer.Management.IntegrationServices, Version=13.0.0.0′,Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.

And another one:

Could not load file or assembly ‘Microsoft.AnalysisServices, Version=13.0.0.0′,Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.

And another one:

Could not load file or assembly ‘Microsoft.AnalysisServices.Core, Version=13.0.0.0′,Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.

And another one:

Could not load file or assembly ‘Microsoft.AnalysisServices.AdomdClient, Version=13.0.0.0′,Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.

This was a kind of nightmare of errors after errors!

Cause

This is something that always annoyed me since Microsoft decided to separate the SSMS from the default installation. So, on one hand, it is great that clients can update to a recent version of the tool. Still, on the other hand, it complicates specific previous simple tasks like performing the configuration of BizTalk Server features in special BAM features.

By design, the latest SSMS only includes the latest version of SSIS assemblies.

I have been using this SSMS 18.4 version in almost all my BizTalk Server installations. But also, in most of my recent installations, my clients didn’t want me to install BAM because they were not using this feature. The reason I use SSMS 18.4 version is that it was the latest version available before BizTalk Server 2020 was released.

I have tried to uninstall this SSMS version and install a previous one – 17.9.1 – to solve this issue, but by doing that, I got a ton of other problems with the basic features of BizTalk Server, like SSO and Runtime. So I decided to keep using SSMS 18.4.

Trying to find the correct version of the SSMS may be an inglorious Task.

I saw comments and suggestions to solve this issue that I would do in a BizTalk Server environment, like:

• Install SSIS on the BizTalk Server machine and re-run the BizTalk configuration.
• Installing the full SSDT (SQL Server Data Tools) ISO on the BizTalk Server machine and re-running the BizTalk configuration.
• Or by installing the Client Tools SDK from SQL Server installation media on the BizTalk machine and re-running BizTalk configuration – this was, in fact, a possible alternative.

But again, this issue is related without a doubt to problems related to assembly reference mismatch.

Solution

You have several solutions on the table to solve this issue. I endup deciding that the simple and fast way was to do an Assembly redirect at the machine level.

To solve this problem by using Assembly Binding Redirection in the machine configuration file (Machine.config), you should change both:

• 32-bit: c:\Windows\Microsoft.NET\Framework\[version]\config\machine.config
• 64-bit: c:\Windows\Microsoft.NET\Framework64\[version]\config\machine.config

And use the following configuration:

<runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
        <dependentAssembly>
            <assemblyIdentity name="Microsoft.AnalysisServices.AdomdClient" publicKeyToken="89845dcd8080cc91" culture="neutral"/>
            <bindingRedirect oldVersion="13.0.0.0" newVersion="14.0.0.0" />
        </dependentAssembly>
        <dependentAssembly>
            <assemblyIdentity name="Microsoft.AnalysisServices" publicKeyToken="89845dcd8080cc91" culture="neutral"/>
            <bindingRedirect oldVersion="13.0.0.0" newVersion="14.0.0.0" />
        </dependentAssembly>
        <dependentAssembly>
            <assemblyIdentity name="Microsoft.AnalysisServices.Core" publicKeyToken="89845dcd8080cc91" culture="neutral"/>
            <bindingRedirect oldVersion="13.0.0.0" newVersion="14.0.0.0" />
        </dependentAssembly>
        <dependentAssembly>
            <assemblyIdentity name="Microsoft.SqlServer.ManagedDTS" publicKeyToken="89845dcd8080cc91" culture="neutral"/>
            <bindingRedirect oldVersion="13.0.0.0" newVersion="14.0.0.0" />
        </dependentAssembly>
        <dependentAssembly>
            <assemblyIdentity name="Microsoft.SqlServer.SQLTask" publicKeyToken="89845dcd8080cc91" culture="neutral"/>
            <bindingRedirect oldVersion="13.0.0.0" newVersion="14.0.0.0" />
        </dependentAssembly>
        <dependentAssembly>
            <assemblyIdentity name="Microsoft.SqlServer.Management.IntegrationServices" publicKeyToken="89845dcd8080cc91" culture="neutral"/>
            <bindingRedirect oldVersion="13.0.0.0" newVersion="14.0.0.0" />
        </dependentAssembly>
    </assemblyBinding>
</runtime>

• Just to be on the safe side, you should add this configuration to both the 32-bit and 64-bit machine configuration files for the .NET Framework 2.0 and 4.0.
• If you are not sure about what is the version of the DLL, you should find the DLL in this path:
  • C:\Windows\Microsoft.NET\assembly\GAC_MSIL\<name>.

Hope you find this helpful! If you liked the content or found it useful and would like to support me in writing more, consider buying (or helping to buy) a Star Wars Lego set for my son. 

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community. View all posts by Sandro Pereira