Do I need to install SSIS on my BizTalk server to configure and use BAM? NO!
The various supporting components required to support BizTalk are very well understood - we need Enterprise Single Sign On, SQL Server Database Services, SQL Server Integration Services if we're running BAM Tools (plus Analysis Services if we're enabling BAM Aggregations), SQL Server Notification Services if we are using BAM Alerts, IIS to support the BAM Portal, etc. What is less well understood is WHERE these components get installed.
When we're installing BizTalk on a development machine or in a lab environment, often we'll install everything to a single box, which is perfectly valid because the load and availability requirements probably do not justify the hardware or setup costs of a multi-server environment. We're more interested in developing our BizTalk solution, so we just select every SQL component between Database Services and Reporting Services, config BizTalk and away we go. But when we move to a non-dev environment (like production or a pre-production environment that simulates the production environment) and start to deploy these components out to different servers, it may be lost as to what goes where. We don't want to install every component on every machine - this would be unnecessary and probably a configuration nightmare.
When you are configuring BAM Tools on a BizTalk server which is utilizing a remote SQL Server (i.e. there are no SQL services of any kind running on the local BizTalk server), you get the following error message:
"Microsoft SQL Server 2005 Data Transformation Services (DTS) for BAM Analaysis is not installed on the local machine. Please install Microsoft SQL Server 2005 Integration Services."
Well, that seems pretty clear. We need to install SSIS on the local BizTalk machine, right? WRONG!
It helps to understand what exactly SSIS's role is in the overall BAM solution. When you deploy a BAM activity, it creates at least one SSIS package for the activity which will create partition tables and move any activity instances within the archive window to the BAMArchive database. If you've created any BAM views which create an OLAP cube, an SSIS package will also be created to move the data between the relational tables and the cube as well. These SSIS packages are stored on the SQL server, in the MSDB database instance on the default server instance (or on a named instance as described here).
So SSIS needs to be installed on the SQL Server not the BizTalk server. But what does need to be installed on the BizTalk server? Only the Management Tools component found under Client Components.
I don't know for sure, but I'm guessing the dependency on the Management Tools is due to the SSIS packages which are dynamically created when the BAM activity is deployed. But this should be the only SQL component that is required to be installed on your BizTalk Server to support BAM Tools.
Happy BAM'ing!
