Remote Execution of SSIS Packages

Overview

When the Jet Data Manager is installed on a separate server and not on the actual SQL Server, then all network traffic when transferring data to the staging database(s) and data warehouse(s) is routed through the server where the Jet Data Manager is installed.  This is due to the fact that SSIS packages are being opened and executed by the Jet Data Manager on the separate server. Even in a high-speed LAN, this is considerably slower than keeping the traffic local on the SQL Server.

With the Remote Execution of SSIS Packages feature, one can force the Jet Data Manager to send the SSIS packages to another server where it will be executed (typically on the actual SQL Server).

Download, Install, and Configure Service

1. Download the required files from the below list of available versions.  Please ensure to choose the one that fits within your environment.

   SQL Server 2005

   • 32 bit
   • 64 bit

   SQL Server 2008 / 2008R2

   • 32 bit
   • 64 bit

   SQL Server 2012

   • 32 bit
   • 64 bit

   SQL Server 2014

   • 32 bit
   • 64 bit

   SQL Server 2016

   • 32 bit
   • 64 bit

   SQL Server 2017

   • 32 bit
   • 64 bit

2. Extract the file labeled ' Remote SSIS Execution ...' and run the setup.

   Run through the windows installer.  The installation process should be completed on the server which hosts your SSIS instance, typically the same server that hosts your Jet Enterprise SQL databases.

3. Once the installer has completed, you will have a new service named Remote SSIS Execution.

   

4. Set the Startup Type to Automatic (Delayed Start) to ensure that the service is started after the SQL Server:

   

5. Navigate to the Log On tab and enter a Username and a Password for the user that should be running the service.

   The Service should be using a login that holds permissions to execute SSIS packages and, if using Integrated Security, read permissions on all data sources and read/write permissions on the Staging database(s) and data warehouse(s).

   Once finished, start the service.

   

6. Configure the logon as users SSISServiceConfig.xml file.

   This file can be located in the service account user’s:

   %APPDATA%\Roaming\ SSISWindowsService\Remote SSIS Execution Service\<Version Number> folder.

   • By default, the service is configured to listen on TCP port 16500.
   • ServerPort: Specify the port number that the service should listen on.
   • 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. Default Setting is True.
   • ADGroup: Specify which Active Directory Security Group that user has to be members of in order to use the service. Default Setting is RemoteSSISOperators.

   

   By default, the service requires the user calling the service to be a member of a specific Active Directory Group.  This requirement along with the name of the group can be changed in the services configuration file: SSISServiceConfig.xml.

Enable Remote SSIS Execution

Once the service is installed and configured, remote SSIS execution can be enabled from the individual Staging Database or Data Warehouse

1. Within the Jet Data Manager, right click your database and edit.

2. Click Advanced... to open the SQL Server Properties dialog.

   

3. Enter the required parameters

   • Use Remote SSIS Package Execution: If checked, the Jet Data Manager will send the package to the service for remote execution.
   • SSIS Service Server: Name of the server where the service is running, Include the complete URL including https:// if you want to use https
   • SSIS Service Path: Information showing the Path. This value cannot be changed.
   • SSIS Service Open Timeout (Minutes): Used when opening channels when no explicit timeout value is specified.
   • SSIS Service Receive Timeout (Minutes): Is not used in the current implementation.
   • SSIS Service Send Timeout (Minutes): Used to initialize the operation timeout, 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.

   Use the Test Service button to test for any connectivity and or permission issues.