The Remote Execution of SSIS Packages feature improves performance when Discovery Hub 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 Discovery Hub machine where Discovery Hub opens and executes the SSIS packages it has generated. Even on a high-speed LAN, this is considerably slower than having
Discovery Hub 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 Discovery Hub to use the remote server for executing SSIS packages. Click the following link to learn more about using Discovery Hub with SQL Server Integration Services
Installing the Remote SSIS Execution Service
To install the Remote SSIS Execution Service on the remote server, follow the steps below.
- Download the Remote SSIS Execution Service that matches the version of Microsoft SQL Server.
- Unzip the content of the installation package to a temporary folder and double-click Setup.exe. Follow the installation instructions to install the service.
- 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.
- On the General tab, in the Startup type list, click Automatic (Delayed Start) to ensure that the service is started after SQL Server.
- 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 and, if using Integrated Security, read permissions on all data sources and read/write permissions on the staging database(s) and data warehouse(s). 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.
- Click OK.
- (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.
- 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. RemoteSSISOperators
- 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 Discovery Hub. To enable Remote Executing of SSIS Packages:
- Right click the data warehouse or staging database you wish to enable the feature on and click Edit.
- At the bottom of the dialogue, click Advanced Settings.
- Select Use remote SSIS package execution to enable the use of the feature.
- 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://.
- In SSIS Service Server Port, enter the port number the service is listening to. Default is 16500
- SSIS Service Path shows the complete URL Discovery Hub will use to connect to the remote
- (Optional) In SSIS Service Open Timeout (Minutes), enter the timeout for opening connections when no explicit timeout is otherwise specified.
- (Optional) In SSIS Service Close Timeout (Minutes), enter the timeout for closing channels when no explicit timeout is otherwise specified.
- SSIS Service Receive Timeout (Minutes) is not used in the current implementation but would control how long Discovery Hub will wait for an answer from the remote server.
- (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.
- 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.