Jaa

SSIS Configuration: Change the SSIS configuration file and add a root level folder to the Stored Packages

  • Artikkeli

Hands-On: Modifying the Default SSIS Package Folders

If you want to store your packages at a different place other than the root folder then by modifying the SSIS configuration file you can achieve this..

The definitions for these folders are stored in the XML file that the SSIS service reads at startup. The SSIS service retrieves the location of this file from the following registry location: HKLM\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile.

With the help of the Configuration file provided by SSIS you can add folders and change the location of the folders.

Note:

  1. The Integration Services service loads the file when the service is started. Any  changes to the registry entry require that the service be restarted.
  2. Incorrectly editing the registry can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from editing the registry incorrectly can be resolved. Before editing the registry, back up any valuable data. For information about how to back up, restore, and edit the registry, see Microsoft Knowledge Base article 891957.
  3. I would also suggest you to go through the article : https://msdn2.microsoft.com/en-us/library/ms137789.aspx

To change the configuration file and add the folder , please follow the below steps.

1. Go to C:\program files\Microsoft SQL Server\90\DTS\Binn\

2. Open file MsDtsSrvr.ini

The file may look like as mentioned below.

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

<DtsServiceConfiguration xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns:xsi="https://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>

3. Go to <Folder xsi:type="FileSystemFolder"> and copy till </Folder>

    Now, paste it below </Folder>. So you will have another set of elements of the

<Folder xsi:type="FileSystemFolder">

4. Now, change <Name> element to “ My SSIS Packages” of the newly copied <Folder> elements and

Modify <StorePath> element with “C:\SSISPackages” (Please create folder C:\SSISPackages”).

5. So the modified configuration file may look like :

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

<DtsServiceConfiguration xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns:xsi="https://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 SSIS Packages</Name>

<StorePath>C:\SSISPackages</StorePath>

</Folder>

</TopLevelFolders>

</DtsServiceConfiguration>

6. Now that you have modified the configuration file, Restart the SSIS service to see the  changes.

To restart the SSIS service please follow the below steps

  1. Go to Control Panel --> Administrative Tools
  2. Look for SQL Server Integration Services
  3. Right click on the SQL Server Integration Services and select Restart
  4. Now open the SQL Server Management Studio and open the Integration Services.
  5. You will be able to see your newly created folder under "Stored Packages --> File System"

Thank you .....& Happy Learning.