Note: The use of SSIS is not recommended in projects with ODX Server.

Introduction

The Remote Execution of SSIS Packages feature improves performance when TimeXtender is installed separately from the SQL Server that houses the data warehouse.

In that setup, all traffic from source systems to the staging databases and data warehouses has to pass through the TimeXtender machine where TimeXtender opens and executes the SSIS packages it has generated. Even on a high-speed LAN, this is considerably slower than having
TimeXtender and the data warehouse on the same server.

As the name suggests Remote Execution of SSIS Packages lets you execute SSIS packages on a remote server, typically the server that houses the target databases, skipping the trip through the application server. 

To use the feature, you need to install a service on the remote server and configure TimeXtender to use the remote server for executing SSIS packages. Click the following link to learn more about using TimeXtender with SQL Server Integration Services

Installation

Installing the Remote SSIS Execution Service

To install the Remote SSIS Execution Service on the remote server, follow the steps below.

1. Download the Remote SSIS Execution Service that matches the version of Microsoft SQL Server.
2. Unzip the content of the installation package to a temporary folder and double-click Setup.exe. Follow the installation instructions to install the service.
3. Next, you need to set up the service. Click/Right-click Start, click Run, type “Services. msc” and click OK. Locate the Remote SSIS Execution service in the list. Right-click the service and click Properties.
4. On the General tab, in the Startup type list, click Automatic (Delayed Start) to ensure that the service is started after SQL Server.
5. On the Log On tab, click This Account and enter the name and password for the user account you wish to use.  
   The service should run with a user account that has permission to execute SSIS packages. You can read more about the required permissions in this article: Configure User Accounts & Permissions
6. By default, the service requires the user calling the service to be a member of a specific Active Directory Group. You can change this - see change advanced settings below.
7. Click OK.
8. (Optional) Change advanced settings, such as the port the service listens to, by editing the config file. Start the service once to have the file created. Then open Explorer
   and navigate to the following folder: %APPDATA%\Roaming\SSISWindowsService\Remote SSIS Execution Service\<version>
   Open SSISServiceConfig.xml in Notepad or another editor.
   
9. You can edit the following properties:
   

   Option	Description	Default Setting
   ServerPort	Specify the TCP port number that the service should listen on.	16500
   CheckUserIsInGroup	If set to True, the service will only allow members of the AD Security Group specified in “ADGroup” to use the service. If set to False, all users can use the service.	true
   ADGroup	Specify which Active Directory Security Group users have to be members of in order to use the service. If the scheduler service is used, the user that runs as that will need to be part of the group as well.	RemoteSSISOperators

10. Save the file and restart the service to apply your changes.

Configuring your project to use Remote Execution of SSIS Packages

When the service is installed and configured, remote SSIS execution can be enabled on the individual staging database or data warehouse in TimeXtender. To enable Remote Executing of SSIS Packages:

1. Right click the data warehouse or staging database you wish to enable the feature on and click Edit.
2. At the bottom of the dialogue, click Advanced Settings.
    
3. Select Use remote SSIS package execution to enable the use of the feature.
4. In Server Name, enter the name of the server where the service is running. If you use HTTPS, please write the complete URL including https://.
5. In SSIS Service Server Port, enter the port number the service is listening to. Default is 16500
6. SSIS Service Path shows the complete URL TimeXtender will use to connect to the remote
   server.
7. (Optional) In SSIS Service Open Timeout (Minutes), enter the timeout for opening connections when no explicit timeout is otherwise specified.
8. (Optional) In SSIS Service Close Timeout (Minutes), enter the timeout for closing channels when no explicit timeout is otherwise specified.
9. SSIS Service Receive Timeout (Minutes) is not used in the current implementation but would control how long TimeXtender will wait for an answer from the remote server.
10. (Optional) In SSIS Service Send Timeout (Minutes), enter the value used to initialize the OperationTimeout, which governs the whole process of sending a message, including receiving a reply message for a request/reply service operation. This timeout also applies when sending reply messages from a callback contract method.
11. Click Test Service to test for any connectivity and/or permission issues click OK to save your changes and click OK in the data warehouse or staging database settings window.