Monday, January 23, 2012

How to set the location of the SSIS top-level folders

Look for the %ProgramFiles%\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml file. Open it in any editor. By default, it will look like this:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="" xmlns:xsi="">
    <Folder xsi:type="SqlServerFolder">
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>

You can change the "." (localhost) to any of your SQL Servers. It's especially useful when this name can't be resolved, e.g. a clustered environment :) Or, if you would like to change the location of the File System folder, just simply replace the "..\Packages" to any other location which is accessible by the Integration Services service account.
You can add, of course, additional top-level folders, if you follow the structure:

    <Folder xsi:type="SqlServerFolder">

Of course, copying a <Folder> block, then changing its content means less effort :)
In this example, the number after the comma is the port (when you don't know the name of the SQL Server instance, or you don't want to use SQL Server Browser because there are only fixed ports set up). If you know the instance name, you can use the servername\instancename format as well.

After you've set up everything, save this xml file, then restart the SSIS service. Your new top-level folders will be available after then.

No comments:

Post a Comment