About #

The following article describes the secure data integration approach and shows the necessary steps to execute SSIS packages accessing an SAP source (Xtract Connection), which is located in a private network (domain) for security reasons.

To perform data integration securely in a private network environment without a direct line-of-sight from the public cloud environment, you can install a self-hosted IR in your on-premises environment behind a firewall, or inside a virtual private network.

The self-hosted integration runtime only makes outbound HTTP-based connections to the internet. The Xtract Connection (SAP connection) with the property ConnectByProxy = True is used to implement access to the local network using the Self-Hosted Integration Runtime (SHIR).

Prerequisites #

The SSIS package was created in advance on a development environment (Visual Studio) and the execution was tested successfully. Subsequently, the SSIS project was published to an SSIS in ADF. The following prerequisites are mostly based on the official Microsoft documentation for using Azure integration runtime (IR) as well as Self-Hosted Integration Runtime (SHIR).

Note: Finally, you download and install the latest version of self-hosted IR, as well as the additional drivers and runtime, on your on-premises machine or Azure virtual machine (VM).

Step-by-step Instruction #

  1. The Azure Data Factory IR is configured and started with the necessary files to run the Xtract IS for Azure extension in ADF.
  2. Download and install the SHIR on the local Windows server (on-prem) -Microsoft Integration Runtime
  3. Register the Integration Runtime (Self-hosted) - Configure a self-hosted IR via UI
  4. If applicable: Enable SSIS package execution on self-hosted IR node using the following cmd command: C:\Program Files\Microsoft Integration Runtime\5.0\Shared>dmgcmd -eesp
  5. Install XtractISSetup.exe to use Xtract IS Data Flow Tasks.
  6. If applicable: Object Linking and Embedding Database (OLEDB), Open Database Connectivity (ODBC), or ADO.NET connectors are used in your packages, download and install the relevant drivers - OLE DB Driver 18 for SQL Server
  7. If applicable: Data flow components from Azure Feature Pack in are used your packages, download and install Azure Feature Pack for SQL Server 2017 - SSIS 2017 Feature Pack for Azure

Note: If the Integration Runtime (Self-hosted) node encouters errors during execution, error logs are provided using the Windows Event Viewer.