BizTalk Server 2020 BAM Configuration: Microsoft SQL Server Integration Services (SSIS) is not installed on the local machine

This could be a valid error message if it weren’t for the simple little detail that we are dealing with a High-availability multi-server BizTalk Server installation. That means that the SQL Server was installed in another machine(s)… and yes, Microsoft SQL Server Integration Services was installed on the SQL Server as Analysis Service and all the pre-requirements of BizTalk Server 2020.

And again, yes. SQL Server Management Studio (SSMS) 18.4 was installed on the BizTalk Server box. So why the hell that when I was trying to configure BAM features, I was getting the following error message:

BizTalk Server 2020 BAM Configuration: Microsoft SQL Server Integration Services (SSIS) is not installed on the local machine. The version of SSIS must be the equivalent version as on the SQL server of the BAM Archive Database. (15)

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

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.

Solution 1: Install side-by-side SSMS 17.9.1

  • Note: do not uninstall SSMS version 18.4. Leave both side-by-side.
  • I know this is a little ridiculous, but it solves the problem.

Solution 2: Assembly redirect at the machine level (machine.config)

  • 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.SqlServer.ManagedDTS" 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\Microsoft.SqlServer.ManagedDTS
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.

Leave a Reply

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

BizTalk360
BizTalk Server

Over 500+ customers across
30+ countries depend on BizTalk360

Learn More
Serverless360
Azure

Manage and monitor serverless
components effortlessly

Learn More
Atomicscope
Business Users

Monitor your Business Activity in iPaaS
or Hybrid integration solutions

Learn More

Back to Top