Hi SQL Server Folks,
After installation of SQL Server 2005 named instance I got an issue while gave a try to connect to Integartion Services. It throwed me below error:-
As you know that Integartion Service always installed as a Default instance, So Question is If I am installing named instance also why I am not able to connect and what is the resolution for this.
The answer is when you tries to connect Integartion Service (IS) then it uses a file name as “MsDtsSrvr.ini.xml” and it’s located in path “%ProgramFiles%\Microsoft SQL Server\<90><100>\DTS\Binn”. If you open that file then it will look like below:-
As you can see in this file that I mentioned four steps in RED COLOUR that you need to change in this file.
But the error that we got is related to Step 2, When you give a try to connect to IS then it will use this file and tries to find the server name becuase server name is only define as dot (.) here then it will try to connect the local machine name i.e. name of the server. So if you installed cluster aware OR named instance of SQL Server then you required to modify this file. So in that case as Step 2 you have to specify SQL Server name \ Instance Name i.e. in file server bname should look like <ServerName> SQL Server Name\InstanceName </ServerName> OR If you installed SQL Server Cluster then it should look like <ServerName> SQL Server Virtual Server \InstanceName </ServerName>
Apart from that we need some more modifications in this file as mentioned below and I also highlighted them in RED in above:-
In Step 1 We need to add an Instance name to the msdb folder and have it like <Name>MSDB instance Name <\Name>.
In Step 3 File System Name should be suffixed with instance Name like <Name>File System InstanceName<\Name>
In Step 4 Full path needs to be used where all pacakges are stored, But do not use dot(.) in this place.
Hope it will Help Someone. Please stay glue for more upcoming stuffs !!!
Filed under: SSIS | 3 Comments »
In Depth SQL : The Real DBA Activities 