Setting up SSIS on a SQL Server which does not have a default SQL instance

  • Article

This one caught me a few weeks ago setting up the infrastructure to support my BAM solution in a new environment.  Although the environment was already setup for BizTalk, it had never been configured for BAM.  I needed to install the SQL Workstation Components on the BizTalk servers, as well as SQL Server Integration Services (SSIS) on the SQL server hosting my BAMPrimaryImport databases, before configuring BAM.  When a BAM activity is deployed, in addition to the tables, views, and stored procedures it creates, it creates a maintenance SSIS package, as well as SSIS packages for each view which creates an OLAP cube in Analysis Services to move the data to the cube from the relational tables.

After installing the SQL components on the BizTalk servers and SSIS on the SQL server, I deployed my BAM activity with success.  However when I went to create a SQL job which would execute my BAM SSIS packages I could not find the SSIS packages which the BAM deployment tool should have created when the activity was deployed.

My customer uses named instances of SQL ONLY - there is never a default instance of SQL deployed on any machine (outside of our developer machines).  However, SSIS by default wants to store its packages in the MSDB database of the default instance of the machine where it was installed.  If the default instance does not exist, the SQL installer does not warn you about this when you install SSIS, nor does the BAM deployment tool throw an error telling you that it was unable to deploy the SSIS packages needed by the solution.

The solution is to point SSIS at one of the named instances of SQL, but where this is accomplished is less than completely obvious.  First, find the MsDtsSrvr.ini.xml file located in %ProgramFiles%\Microsoft SQL Server\90\binn.  Open the file in Notepad and navigate to the <ServerName> element of the XML file.  The default value will be a ".", indicating the local machine/default instance of SQL.  Replace this value with your fully qualified SQL instance name (e.g.. "MachineName\InstanceName,Port"), close and save the file, and restart SSIS>

Once I had done this, and updated the activity, I could find my BAM SSIS packages as stored in the MSDB database on my NAMED SQL instance.