SSIS Error : The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic “Configuring the Integration Services Service” in SQL Server 2008 R2 Books Online

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 !!!

Advertisements

3 Responses

  1. Hy,

    Thank you so much, i lost hours trying to find the solution to this problem….well, it worked!

    I think you should add one more step to your solution, we need to restart SSIS Service before we try to connect again so that it recognizes this changes.

  2. Thanks… My SSIS was using a wrong instance of MSDB…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: