Does BAM Require Local Installation of SSIS? No!
Today I helped a customer install BizTalk Server 2006, they did not have much experience with BizTalk so I was assigned to assist them. We followed the BizTalk Server 2006 Installation and Upgrade Guides documents, mostly the multi-server version but the single-server Windows Server 2003 is also useful for reference (as part of the multi-server version is too complex due to fail-over cluster configurations). What I wanted to share was one of the problems that was came across and how we solved it (ever heard of RTFM?).
Installation was done and we the had BizTalk Server Configuration application started. We had entered all information about groups, accounts, databases, ... But one problem still remained, there was an an error related to BAM Analysis. When when we clicked on the error this window was shown.
For the benefit of the search engines I'll also type the text:
Microsoft SQL Server 2005 Data Transformation Services (DTS) for BAM Archiving is not installed on the local machine. Please install Microsoft SQL Server 2005 Integration Services. (Microsoft.BizTalk.Bam.CfgExtHelper.ToolsHelper)
Could not load file or assembly 'Microsoft.SqlServer.ManagedDTS, Version=188.8.131.52, Culture=neutral, PublicKeyToken=89845dc8080cc91' or one of its dependencies. The system cannot find the file specified. (Microsoft.BizTalk.Bam.CfgExtHelper)
Even after reading this message multiple times we did not really understand what the problem was. The error clearly states that SQL Server 2005 Integration Services (SSIS) should be installed on the same machine as BizTalk is running on, but that just sounds silly! After having verified that the remote SQL Server actually had SSIS installed and that the Microsoft.SqlServer.ManagedDTS assembly really was missing, we started suspecting that we had taken a short-cut during installation of SQL Server Client Tools (one of the pre-requisites for BizTalk).
We modified the client tools options to also include the Business Intelligence... and the Management Tools options. While still keeping BizTalk Server Configuration application open we once again clicked Apply Configuration, but we still got the same error. I thought that perhaps the configuration application caches this information, so we restarted the application. After having imported the previous configuration we once again clicked Apply Configuration. And it worked!
When reading the documents referenced above they clearly list which options in the client tools installation that should be included. Business Intelligence... is not included but the Management Tools is. The conclusion is that the Microsoft.SqlServer.ManagedDTS assembly is installed and registered as part of the Management Tools option, but we cannot be 100% sure.
Disclaimer: The order of our actions are somewhat modified to be able to properly illustrate how the problem can be solved.
The error indicate that due to the BizTalk requires managed (i.e. .NET) access to DTS we need to install SQL Server 2005 Integration Services on the same machine as BizTalk. But the actual error is that the Management Tools option in SQL Server 2005 Client Tools has not been installed. This is clearly described in the documentation, so this was a case of RTFM.