Create MSDB directory for storing SSIS packages from multiple instances (located in the msdb database of each of the instance):

 

Go to C:\Program Files\Microsoft SQL Server\100\DTS\Binn\ folder (SQL Server 2008) and edit the MsDtsSrvr.ini.xml file:

 

<?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 INSTANCE_1</Name>

      <ServerName>SERVER_NAME\INSTANCE_1</ServerName>

    </Folder>

 

    <Folder xsi:type="SqlServerFolder">

      <Name>MSDB INSTANCE_2</Name>

      <ServerName>SERVER_NAME\INSTANCE_2</ServerName>

    </Folder>

 

     <Folder xsi:type="FileSystemFolder">

      <Name>INSRANCE_1 File System</Name>

      <StorePath> C:\FOLDER_SSIS\INSTANCE_1</StorePath>

    </Folder>

 

    <Folder xsi:type="FileSystemFolder">

      <Name>INSRANCE_2 File System</Name>

      <StorePath>C:\FOLDER_SSIS\INSTANCE_2</StorePath>

    </Folder>

  </TopLevelFolders> 

</DtsServiceConfiguration>

 

After modifying the file according to your needs (instance name, path to locally stored package), restart the SQL Server Integration Services service.

 

If after the restart, the directory structure is not visible go the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\ServiceConfigFile. This key specifies the location and name for the configuration file that Integration Services service uses.  If value is empty then add in:

C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml

You can also update the value of the registry key to use a different name and location than the default one for the configuration file.

Any changes to the Registry entry require a restart of the Integration Services service in order to reload the configuration file.

 

If after this new restart the directory structure is still not visible you must check the event viewer for any error messages. Usually the configurations file (MsDtsSrvr.ini.xml) has typing errors:

?Microsoft SSIS Service configuration file is incorrect.

Error reading config file: There is an error in XML document (1, 5).

 Loading server with default settings.?  

Check the configuration file and correct any possible typing errors, then restart the SQL Server Integration Services service.

 

For more details regarding the Integration Services Service configuration you can access the following link:

http://msdn.microsoft.com/en-us/library/ms137789(v=sql.105).aspx

 


No feedback yet

Leave a comment


Your email address will not be revealed on this site.
(Line breaks become <br />)
(For my next comment on this site)
(Allow users to contact me through a message form -- Your email will not be revealed!)