Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).

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.

Comments

Posted by Amit Gajjar on 1 November 2012

Your blog link is broken....

Posted by Devin Knight on 3 December 2012

Sorry my blog got moved to this link.  www.bidn.com/.../adding-a-folder-to-the-ssis-package-store

Leave a Comment

Please register or log in to leave a comment.