BizTalk Server 2020 Configuration: Error validating the SSIS Catalog database. Please ensure SQL Server Integration Services is installed on the local machine and SSIS Catalog is created on target SQL Server

Following my last blog post and while I was trying to fix some issues regarding a BizTalk Server 2020 installation and configuration provided by my intern as is learning process. We end up solving some critical problems that allow us to install the platform’s most important components. Nevertheless, several components like BAM Tools, BAM Portal and BizTalk EDI/AS2 Runtime failed to configure:

Once, we inspect the logfile provide the the BizTalk Server Configurarion Wizard we found the following error message:

[2020-11-10 22:17:18:0674 Error Configuration Framework]Feature: [BAM Tools] Failed to configure with error message [Feature is skipped due to failed validation.  Please go to Custom Configuration to fix the validation error.

Validation Error: <Exception Message=”Error validating the SSIS Catalog database. Please ensure SQL Server Integration Services is installed on the local machine and SSIS Catalog is created on target SQL Server.” Source=”BAMTools” HelpID=”idsErrorValidateSSISCatalogDatabase”><Exception Message=”SSIS Catalog (SSISDB) does not exist on server BTS2020LABAVMRG. Please create SSIS Catalog.” Source=”Microsoft.BizTalk.Bam.CfgExtHelper.ToolsHelper” HelpID=”error_SSISCatalogNotExists”/></Exception>]

[2020-11-10 22:17:18:0674 Error Configuration Framework]Feature: [BAM Portal] Failed to configure with error message [The Configuration of Feature ‘BAM Portal’ failed because the dependent Feature ‘BAM Tools’ was not configured. ]

[2020-11-10 22:17:18:0674 Error Configuration Framework]Feature: [BizTalk EDI/AS2 Runtime] Failed to configure with error message [Feature is skipped due to dependent feature (BAM Tools) failed to configure correctly.]

Three distinct errors but the last two are a sequence of the first one, all of them are related to the same cause.

Cause

SSISDB was automatically created during the BizTalk Server configuration process on previous versions if we enable BAM Portal. However, BAM Portal was deprecated in this new BizTalk Server version, and the configuration process no longer created the SSIS Catalog. Instead, we need to create the catalog manually.

If you don’t create the SSIS Catalog manually, you will not be able to configure BAM Portal. Nevertheless, you can always perform this operation whenever you think is required, and after that, configure BAM Portal.

Make sure you also configure SQL Server Database Mail feature if you wish to configure BAM Alerts on your BizTalk Server 2020 environment

Solution

The solution is very simple, we need to manually create the catalog by using the following instructions:

  • Open SQL Server Management Studio and connect to the SQL Server Database Engine.
  • In Object Explorer, expand the server node, right-click the Integration Services Catalogs node, and then click Create Catalog.
  • On the Create Catalog window, do the following configuration and click OK.
    • Select Enable CLR Integration option.
    • Enter a password to protect the encryption key.

And run the BizTalk Server Configuration again. At the end you will be able to configure all BizTalk Server components.

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.

3 thoughts on “BizTalk Server 2020 Configuration: Error validating the SSIS Catalog database. Please ensure SQL Server Integration Services is installed on the local machine and SSIS Catalog is created on target SQL Server”

  1. Biztalk 2020 – I’m using group managed service accounts for all host instances and biztalk pplication web pools – working well, with one exception The BAM pool I built two servers and they were fine following bam pool configuration. After some time I have gone in later, and the pool is stopped, the configuration shows a red X.. I reconfigure all is green, and stops again. looking over configuration I see all managed accounts not showing domain\ManagedAcc on them except for the bam pool, which appears as domain\ManagedAcc$ is there a gui issue? I checked via powershell and my servers are authorized to retrieve pwds. I know the managmnt account cannot be managed, and I also note on a 3rd server where I used classic account (not managed) I have had no issue with BAM app working without issue.

    1. I didn’t try yet using GMS accounts. You can open an incident in Microsoft but the thing is that BAM Portal is deprecated so I don’t know if they will solve this issue. But you can try it.

  2. Thanks for an excellent article. It seems like SSIS and BAM for BizTalk 2020 might be buggy on the whole. After overcoming that issue, you’re left with the BAM Archiving functionality to keep your BAM DB nice and tidy. But at least for us, that doesn’t seem to work, SSIS keeps throwing up all kinds of errors when we try running it in our QA environment. Our test environment works fine, but there both BizTalk 2020 and SQL Server 2019 are on the same server. But in QA it’s on a 2 node Availability Group and either AG’s OR SSIS isn’t playing nice at all with BizTalk 2020.

    Have you had the same experience?

Leave a Reply

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

turbo360

Back to Top