Configure SSIS to use Named Instances

0

SQL Server family has various services like Database Services (SQL Server), Analysis Services, Reporting Services which supports Named Instance of the services but there are few others like Integration Services which does not support named instance. This means, we can have one and only one service of Integration Services on each server.

By default, the MSDB folder of Integration Services points to MSDB of default instance of SQL Server. But we can change this to point any (one or more than one) named instance of SQL server.

To point MSDB folder of Integration Services to any other instance of SQL Server, we need to change the configuration file (MsDtsSrvr.ini.xml). Following are the steps to do that:

1.Browse the file MsDtsSrvr.ini.xml. Depending on Version of SQL Server installed, you can get this at C:\Microsoft SQL Server\90\DTS\ or C:\Microsoft SQL Server\100\DTS\

2.Create a backup copy of the file.

3.Open the configuration file in a text editor like BIDS, NotePad.

4.To configure a named instance instead of the default instance, change the existing ServerName entry to ServerName\InstanceName under the Folder node of type SQLServerFolder.

5.To configure a named instance in addition to the default instance, add a new Folder node of type SQLServerFolder under the TopLevelFolders node.

6.Save and close the configuration file.

7.Restart the SSIS service.

Vaibhav Gupta

%d bloggers like this: