Blog Post

Adding a folder to the SSIS Package Store

,

The SSIS Package Store is a nice option available for storing SSIS packages.  The Package Store lists packages stored on the server in the MSDB folder and all packages stored on the file system as long as they are in the default folder (C:\Program Files\Microsoft SQL Server\100\DTS\Packages for SQL 2008) in the File System folder.  View my regular blog to see screenshots included http://blogs.pragmaticworks.com/devin_knight/.

It is possible to adjust the Package Store to look for packages in different file system folders or different server instances.  To do this you must change the MsDtsSrvr.ini.xml file in the C:\Program Files\Microsoft SQL Server\100\DTS\Binn (folder is in SQL 2008 only). 

It’s a pretty simple change that you can even use notepad to do.  Here are the steps to follow to add a new File System folder to the Package Store.

1. Navigate to the file C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml and open it notepad or any program that can edit an xml file.

2. To add a second File System folder Package Store add the following underlined code with the file path you want between <StorePath> and </StorePath> (Be careful when making these changes.  You may want to make a copy of the file before making changes):

<?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>
    <Folder xsi:type="FileSystemFolder">
      <Name>My Packages</Name>
      <StorePath>C:\SSIS</StorePath>
    </Folder>

  </TopLevelFolders>
</DtsServiceConfiguration>

3. If you want to change to a different server instance then type the new server instance between <ServerName> and </ServerName>.

4. After you have made these changes and saved the file restart the SQL Server Integration Services in the SQL Server Configuration Manager.

Your new folder will not appear until you restart the service.  You can connect to Integration Services in Management Studio and find your new folder.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating