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

Recently I wrote a blog post about BizTalk Server 2020 BAM Configuration: Microsoft SQL Server Integration Services (SSIS) is not installed on the local machine. This blog is the sequence of that one since the errors I faced on that installation went way belong to that error. And yes, Microsoft SQL Server Integration Services and SQL Server Analysis Services were installed on the SQL Server, and SQL Server Management Studio (SSMS) 18.4 was installed on the BizTalk Server box.

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 a 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 because 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 based features of BizTalk Server like SSO and Runtime. So I decide 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:

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

But again, this issue is related without a doubt with problems related to assemblies 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 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 in both 32 and 64-bit in .NET Framework 2.0 and 4.0 machine configuration files.
  • If you are not sure about what is the version of the DLL, you should find the DLL on this path:
    • C:\Windows\Microsoft.NET\assembly\GAC_MSIL\<name>
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.

1 thought on “BizTalk Server 2020 BAM Configuration: Could not load file or assembly Microsoft.SqlServer.SQLTask”

  1. Hi
    Even after changes to machine.config the error does not go off.
    Analysis server is installed remotely.
    Following is the error:
    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. (Microsoft.BizTalk.Bam.CfgExtHelper)

Leave a Reply

Your email address will not be published. Required fields are marked *

turbo360

Back to Top