Wednesday, 16 January 2008

Deploying SSIS Packages with Named Instances

Unlike in SQL Server 2000, you deploy SSIS Packages to the SSIS Service, which is a standalone single-service; regardless of how many instances of SQL Server you have installed. When deploying packages to be stored in the MSDB database (as opposed to the file system) the Package deployment wizard will check the appropriate settings in the XML config file '\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml'; it is this setting that determines under which instance your Package will be deployed.

By default however, the config file points to the default instance of SQL Server, that is to say if you only have named instances installed, the SSIS Service will be pointing at an instance that doesn't exist.

Deploying a package under these circumstances will generate errors that appear to be caused by a lack of access or privileges, but in reality is caused by a missing default instance; which is fixable.

Navigate to: \Microsoft SQL Server\90\DTS\Binn\ and open the file MsDtsSrvr.ini.xml in notepad, locate the XML tags labelled and modify its value to be the correct server and instance name in the format [servername]\[instancename] there is no need to use IP's and Port Numbers.

You now need to save the file, and stop/start the SQL Server Integration Services service.