Maintenance plan exporting problem on named instance

Problem

I installed SQL server 2005 Developer edition with a named instance on my computer. I created a database in order to check some SQL server advanced tools. I created a maintenance plan to backup the DB and some other things. The problem occurred when I decided that I want to export the maintenance plan from MSDB to FS and copy it onto another computer. When I tried to connect to Integration services with my named instance (server name = instance name), I got an error:

Then I tried to connect only to a server name and I succeeded to connect, but when I tried go to Stored Packages -> MSDB -> Maintenance Plans, I got this error:

I checked the Configuration Manager and everything was OK…  I tried to restart the service, but it didn’t help.  I tried to write “.” instead of server name, but it didn’t work either.

Solution

From the first error I understood that SSIS doesn’t support multi-instance, but I had only one instance and it was named.  From the second error I understood that SSIS installation is good, because I managed to connect to SSIS with only a server name, but when I tried to go to the MSDB, I got an error.  This happened because there is no MSDB on this server (MSDB is on a named instance), so I can’t connect to it.  When I tried to connect with “. “ as the server name, I succeeded to connect, but got the same error when I tried to go to MSDB. I understood that the solution must be in the configuration of SSIS connection to the server.  So I found a server configuration file named MsDtsSrvr.ini.xml in this path C:Program FilesMicrosoft SQL Server90DTSBinn. The file contains:

XHTML

<?xml version="1.0" encoding="utf-8"?> <DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">   <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>   <TopLevelFolders>     <Folder xsi:type="SqlServerFolder">       <Name>MSDB</Name>       <ServerName>.</ServerName>     </Folder>     <Folder xsi:type="FileSystemFolder">       <Name>File System</Name>       <StorePath>..Packages</StorePath>     </Folder>   </TopLevelFolders>� </DtsServiceConfiguration>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

<?xml version="1.0" encoding="utf-8"?>

<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

<TopLevelFolders>

<Folder xsi:type="SqlServerFolder">

<Name>MSDB</Name>

<ServerName>.</ServerName>

</Folder>

<Folder xsi:type="FileSystemFolder">

<Name>File System</Name>

<StorePath>..Packages</StorePath>

</Folder>

</TopLevelFolders>�

</DtsServiceConfiguration>

You can see that there is server name configuration there and its “.”, it means that SSIS connects to the server name only.  I changed “.” to a server nameinstance name and saved the changes.

XHTML

<?xml version="1.0" encoding="utf-8"?> <DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">   <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>   <TopLevelFolders>     <Folder xsi:type="SqlServerFolder">       <Name>MSDB</Name>       <ServerName>Mycomputernatalie</ServerName>     </Folder>     <Folder xsi:type="FileSystemFolder">       <Name>File System</Name>       <StorePath>..Packages</StorePath>     </Folder>   </TopLevelFolders>� </DtsServiceConfiguration>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

<?xml version="1.0" encoding="utf-8"?>

<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

<TopLevelFolders>

<Folder xsi:type="SqlServerFolder">

<Name>MSDB</Name>

<ServerName>Mycomputernatalie</ServerName>

</Folder>

<Folder xsi:type="FileSystemFolder">

<Name>File System</Name>

<StorePath>..Packages</StorePath>

</Folder>

</TopLevelFolders>�

</DtsServiceConfiguration>

Then I stopped and started SSIS service. I tried to connect again with “.” as server name and I succeeded. I managed to go to Stored Packages -> MSDB -> Maintenance Plans and see my Maintenance plan and to export it as I planned.  And I learned something new….

#configuration #SSIS #maintenance