Is it mandatory to install SSIS on SQL Server hosting BizTalk Databases

  • Article

I was working with a customer recently where they do not want to install SSIS on the SQL Server hosting BizTalk databases, instead the requirement was to have a separate server dedicated to SSIS which will run the BAM packages. At the first instinct, it seemed like that this is not possible and we need to have SSIS on the SQL Server otherwise we cannot deploy any BAM package using bm.exe. While working on it, we figured out that it is not mandatory to have SSIS on the same SQL Server hosting BizTalk databases and we figured out some more pieces of it. So I thought this blog post will help if you are not aware of it.

Basically, when we deploy a BAM Activity, it creates the packages at the following location:

1. Data Maintenance(DM) packages are stored on the BAM Archive Database server.
2. Analysis(AN) packages are stored on the BAM Star Schema Database server.

bm.exe also needs a few components from SSIS management API's to create BAM packages, therefore the servers from where you deploy (typically it will be BizTalk Server) also need to install them. You can go through the following posts if  you are interested to find out what is the minimum requirement for the server from where you run bm.exe: https://blogs.msdn.com/joscot/archive/2009/05/25/do-i-need-to-install-ssis-on-my-biztalk-server-to-configure-and-use-bam-no.aspx and https://blogs.msdn.com/joscot/archive/2009/08/04/an-update-to-bam-ssis-pre-requisites.aspx

So now the question is where the SSIS package will be deployed if you do not have the SSIS installed on the SQL server (star schema, archive). These SSIS packages are stored in the MSDB database. If you are interested to know where it will be stored if you are running a named instance of SQL, I will recommend to go through another blog post by John Scott: https://blogs.msdn.com/b/joscot/archive/2009/05/25/do-i-need-to-install-ssis-on-my-biztalk-server-to-configure-and-use-bam-no.aspx

Now, you have got the SSIS packages stored in the MSDB database of SQL Server (star schema, archive). You can now remotely run these packages from another server which have got SSIS installed on it. A SSIS package runs on the same computer which starts the package. If a remote package is directly run from a local computer, the package will load and run from the local computer itself. As long as we have the right permissions, the packages should be able to run from anywhere. Considering these, SSIS must be installed on the server from where you plan to run these packages. You can go through https://blogs.msdn.com/b/michen/archive/2006/08/11/package-exec-location.aspx for more details on it.

We can now say that it is not mandatory to have SSIS on the SQL Server hosting BizTalk Databases and it is very much possible to have a dedicated SSIS Server to run these packages. Thanks to Rajat for providing me the information!

Atin Agarwal