Connect to SSIS remotely and edit default location where packages are stored

There may be situations when some users have to connect to SSIS remotely form the workstations because users cannot be logging into the actual servers. There are some steps that need to be done for having users to successfully connect to SSIS. Else they may get an error message

Perform the below steps to change the configuration of how SSIS accepts remote connections in a cluster

Note that the first step is only required for those instances in a cluster and it did not require me to make these changes to connect to a server not in cluster.

1. Search for the MsDTSsrvr.ini file on the server and open it with notepad:

The content of the .ini 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</Name>

      <ServerName>servername\instancename</ServerName>

    </Folder>

    <Folder xsi:type="FileSystemFolder">

      <Name>File System</Name>

      <StorePath>M:\MSSQL$SQL3\SSISPackages</StorePath>

    </Folder>

  </TopLevelFolders> 

</DtsServiceConfiguration>

Edit the highlighted ones in red as required.

The store path is by default D:\AppWin32\MSSQL\90\DTS\Packages or C:\Program Files\Microsoft SQL Server\90\DTS\Packages unless you wish to modify like in the above case. That location is where the packages get stored when you import into a file system and not msdb.

When we import any package into the msdb d/b there is no physical .dtsx file stored on the server.

1. Add the user/group to Distributed Com group on the server

2. Go to Admin tools -- Component services -- Computers -- My computer -- DComConfig -- MsDtsSrvr, right click on it and hit properties

3. Go to ‘Security’ tab and edit the first 2 sections: ‘Launch and Activation Permissions’ and ‘Access Permissions’ and hit edit to add/remove users as required.