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….
Comments